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
Bookmarks