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:
![Please see the screenshot here for the error that comes up](https://i.stack.imgur.com/M1bop.png)

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!