Feb 21 2022 07:29 AM - edited Feb 21 2022 07:33 AM
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