Using VBA, can you enter a formula which refers to another workbook with a dynamic name?

Question

I am using 2 files. Both files are exactly the same, but have been refreshed on different days. This file is refreshed every working day and a copy is saved with the date of refresh.

In the newest file, I want to compare the numbers from the file which was refreshed 7 days before the date of refresh in the current file. I will do this using a =VLOOKUP() formula. I have a formula in cell "I1" which contains the name of the file which was refreshed 7 days prior to the current file. I want to use this cell as a reference in my VBA to find data from.

Here is my current code:

' defining the two worksheets
    Dim WB1 As Workbook
    Dim WB2 As Workbook
'   Capture current workbook
    Set WB1 = ActiveWorkbook

    [CODE TO FILTER PIVOT TABLE OF WB1]

'Open Last week's MS Sales File
    Workbooks.Open FileName:="C:\Users\[My Username]\W\X\Y\Z\" & Range("I1") & ".xlsm"
    Set WB2 = ActiveWorkbook
    WB2.Activate

    [CODE TO FILTER PIVOT TABLE OF WB2]

    Range("E29").Select
    ActiveCell.FormulaR1C1 = "L.W"
    Range("F29").Select
    ActiveCell.FormulaR1C1 = "Change"
    Range("E30").Select
    ActiveCell.FormulaR1C1 = _
    "=IFERROR(VLOOKUP(RC[-3],'[WB2]Software'!R30C2:R163C4,3,0),0)"

    [MORE CODE to compare values from vlookup]

The code works when I simply enter the file name of a particular file from a particular date. For example:

"=IFERROR(VLOOKUP(RC[-3],'[BASE NAME_ddmmyy.xlsm]Software'!R30C2:R163C4,3,0),0)"

However, the "ddmmyy" needs to update based on the value in cell "I1" in WB1, which contains the date from 7 days before the date in WB1.

ERROR:

The code stops and shows me this ^. It takes me to File Explorer in my "Documents" folder and says "Update Values: WB2". Even though WB2 is already open, it doesn't open and activate the file and then choose the appropriate range for the vlookup.

Any help or support is appreciated!

Answer 1

The Range.Address Property has a very useful argument called External. Setting this to True, it will include both Workbook and Worksheet name.

I have made some additional changes, so that you can Avoid Using Select in Excel VBA

Range("E29").Value= "L.W"
Range("F29").Value = "Change"
Dim SourceSheet AS Worksheet
Set SourceSheet = wb2.Worksheets("Software")
Range("E30").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-3]," & _
    SourceSheet.Range(SourceSheet.Cells(30,2), SourceSheet.Cells(163,4)).Address(True, True, xlR1C1, True) & _
    ",3,0),0)"

Answer 2

I think you want

ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-3],'[" & WB2.Name & "]Software'!R30C2:R163C4,3,0),0)"