VBA- Formula to reference another workbook

%3CLINGO-SUB%20id%3D%22lingo-sub-3193895%22%20slang%3D%22en-US%22%3EVBA-%20Formula%20to%20reference%20another%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3193895%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHey%20everyone%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20a%20Formula%20code%20that%20works%2C%20see%20below.%3C%2FP%3E%3CP%3ENow%20I%20want%20that%20formula%20to%20reference%20to%20another%20closed%26nbsp%3B%20%22Source%20workbook%22%2C%20not%20the%20workbook%20the%20makro%20is%20in.%3CBR%20%2F%3EI%20already%20achieved%20copying%20from%20that%20%22Source%20workbook%22%20but%20I%20want%20to%20immediatly%20apply%20the%20formula%20onto%20teh%20data%20in%20the%20soure%20workbook%20without%20needing%20to%20copy%20it.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EThe%20Formula%20Code%3A%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3ESub%20Formula_Active_Row()%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EDim%20r%20As%20Long%2C%20rr%20As%20Long%2C%20c%20As%20Long%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EDim%20wrng%20As%20String%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3Er%20%3D%202%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3Err%20%3D%20Cells(Rows.Count%2C%20%22D%22).End(xlUp).Row%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3Ewrng%20%3D%20%22%24D%24%22%20%26amp%3B%20r%20%26amp%3B%20%22%3A%24D%24%22%20%26amp%3B%20rr%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ECells(ActiveCell.Row%2C%204).Formula%20%3D%20%22%3DAVERAGEIF(%22%20%26amp%3B%20wrng%20%26amp%3B%20%22%2C%22%22%26gt%3B0%22%22%2C%22%20%26amp%3B%20wrng%20%26amp%3B%20%22)%22%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EEnd%20Sub%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20for%20a%20VBA%20newbie%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20help%20is%20much%20appreciated%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EFYI%3A%20Here%20is%20the%20code%20to%20Copy%20from%20the%20%22Source%20Workbook%22%3C%2FP%3E%3CP%3E%3CSPAN%3EOption%20Explicit%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EDim%20Stamm%20As%20String%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EDim%20Quelldatei%20As%20Variant%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3ESub%20Aktualisieren2()%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EStamm%20%3D%20ActiveWorkbook.Name%20'%20'Is%20target%20File%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EQuelldatei%20%3D%20%22Source.xlsx%22%20'Is%20Source%20File%20%26amp%3B%20Needs%20to%20change%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EWorkbooks.Open%20Filename%3A%3D%22C%3A%5CUsers%5CSource%5C%22%20%26amp%3B%20Quelldatei%20'Folder%20of%20Source%20File%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EWorkbooks(Quelldatei).Sheets(%22Source%20January%22).Range(%22A1%3AAH33%22).Copy%20'%20Adapt%20Sheet%20and%20Range%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EWorkbooks(Stamm).Sheets(%22Formula%22).Range(%22A1%3AAH33%22).PasteSpecial%20xlPasteValues%20'%20Adapt%20Sheet%20and%20Range%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EApplication.CutCopyMode%20%3D%20False%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EWorkbooks(Quelldatei).Close%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EEnd%20Sub%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3193895%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Hey everyone,

I have a Formula code that works, see below.

Now I want that formula to reference to another closed  "Source workbook", not the workbook the makro is in.
I already achieved copying from that "Source workbook" but I want to immediatly apply the formula onto teh data in the soure workbook without needing to copy it.

The Formula Code:

Sub Formula_Active_Row()
Dim r As Long, rr As Long, c As Long
Dim wrng As String

r = 2
rr = Cells(Rows.Count, "D").End(xlUp).Row
wrng = "$D$" & r & ":$D$" & rr
Cells(ActiveCell.Row, 4).Formula = "=AVERAGEIF(" & wrng & ","">0""," & wrng & ")"

End Sub

 

Any ideas for a VBA newbie??

 

All help is much appreciated



FYI: Here is the code to Copy from the "Source Workbook"

Option Explicit
Dim Stamm As String
Dim Quelldatei As Variant

Sub Aktualisieren2()
Stamm = ActiveWorkbook.Name ' 'Is target File
Quelldatei = "Source.xlsx" 'Is Source File & Needs to change
Workbooks.Open Filename:="C:\Users\Source\" & Quelldatei 'Folder of Source File
Workbooks(Quelldatei).Sheets("Source January").Range("A1:AH33").Copy ' Adapt Sheet and Range
Workbooks(Stamm).Sheets("Formula").Range("A1:AH33").PasteSpecial xlPasteValues ' Adapt Sheet and Range
Application.CutCopyMode = False
Workbooks(Quelldatei).Close

End Sub

0 Replies