Referencing a workbook stored in a variable in a formula

[Jacek]

Hi,

I've got a macro that opens multiple workbooks. My macro is to take the value from the same cell from the same worksheet from each workbook, average it and return that value in a particular cell in the main workbook, that contains the macro.

My issue is that I cannot seem to properly refer to the multiple workbooks that have been opened. We don't always know the exact name of the files that are being opened, so I have the macro assign a WORKBOOK variable to each file it opens. The problem is coming up where the average formula can't understand which workbooks I am referring to. Please take a look:

Public Day1open As String
Public Day2open As String
Public Day3open As String
 
Public Day1 As Workbook
Public Day2 As Workbook
Public Day3 As Workbook
 
Day1open = MsgBox("Please select day 1", vbYesNo)
If Day1open = vbYes Then
    Day1File = Application.GetOpenFilename(, , , , False)
    Workbooks.OpenText Filename:=Day1File
    Set Day1 = ActiveWorkbook
End If
Day2open = MsgBox("Please select day 2", vbYesNo)
If Day2open = vbYes Then
    Day2File = Application.GetOpenFilename(, , , , False)
    Workbooks.OpenText Filename:=Day2File
    Set Day2 = ActiveWorkbook
End If
Day3open = MsgBox("Please select day 3", vbYesNo)
If Day3open = vbYes Then
    Day3File = Application.GetOpenFilename(, , , , False)
    Workbooks.OpenText Filename:=Day3File
    Set Day3 = ActiveWorkbook
End If
 
' here I go to the main workbook where i'd like the averaged values to be returned
 
Range("d4").Formula = "=average('[Day1]RF M24 REFERENCE'!$D$4,'[Day2]RF M24 REFERENCE'!$D$4,'[Day3]RF M24 REFERENCE'!$D$4)

Thanks in advance

 

[Jacek]

solved