I am looking to add a validation by comparing the total of key columns imported with the below macro. I have the formula created, however as you can image the link (ie filename) is required. to reference the file name and path of the imported file?

Is there a way to add line in the marco to reference the file set by the VBA code "Set wb = Workbooks.Open(sWb)"?

I think it would be something like:


    Range("A3").Select
    ActiveCell.FormulaA1B1 = "=CELL(""filename (Workbooks.Open(sWb) )"
Here is the Macro VBA Code:

Option Explicit

Sub UpdatePivotTables()
'
' Prompts User to select file and import into Automated Report
    
Application.ScreenUpdating = False
Dim sFil   As String
Dim sTitle As String
Dim sWb    As String
Dim iFilterIndex As Long
Dim wb As Workbook
Dim wsRpt As Worksheet
Dim wsNum As Worksheet
On Error GoTo err_handler
'Setup
    sFil = "Excel Files (*.xls),*.xls"                              'list of file filters
    iFilterIndex = 1                                                'Display *.xls by default
    sTitle = "Select this week's BRT Weekly Resource Report."       'caption
    sWb = Application.GetOpenFilename(sFil, iFilterIndex, sTitle)   'filename
    If sWb = "False" Then Exit Sub
    Set wsNum = ThisWorkbook.Sheets("No of Wkdays Calc") 'Num of days target sheet
    Set wsRpt = ThisWorkbook.Sheets("BRT Report")        'Weekly Resource Report sheet
    
'Open file and copy data
    Set wb = Workbooks.Open(sWb)
    wb.Sheets("BRT Report").Range("A4:BB3000").Copy wsRpt.Range("A4")
    wb.Sheets("No of Wkdays Calc").Range("A1:BB3000").Copy
    wsNum.Range("A1").PasteSpecial xlPasteValues
    wb.Close False

' Updates the Pivot table Cache and data being reported in the Weekly Resource Report.
    With ThisWorkbook.Sheets("Pivot by Division Detail")
        .PivotTables("PivotTable1").PivotCache.Refresh
        .Range("C13").Select
        .PivotTables("PivotTable1").PivotCache.Refresh
    End With
    ThisWorkbook.Sheets("Pivot by Region").PivotTables("PivotTable1").PivotCache.Refresh

' Return to instruction tab
    ThisWorkbook.Sheets("Instructions").Activate
    Range("A1").Select
    Application.ScreenUpdating = True
    Exit Sub

err_handler:
    Application.ScreenUpdating = True
    MsgBox "No selection made"
End Sub