I am looking to (1) close this file and (2) simplify\clean up the overall macro. Any recommendations.
****************************************************
Option Explicit
Sub UpdatePivotTables()
'
' UpdatePivotTables Macro
' Macro recorded 2/28/2011 by Gary Gremel
' Keyboard Shortcut: Ctrl+Shift+U
' 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 Integer
On Error GoTo err_handler
' Set up list of file filters
sFil = "Excel Files (*.xls),*.xls"
' Display *.xls by default
iFilterIndex = 1
' Set the dialog box caption
sTitle = "Select this week's BRT Weekly Resource Report."
' Get the filename
sWb = Application.GetOpenFilename(sFil, iFilterIndex, sTitle)
Workbooks.Open Filename:=sWb
' Copies BRT Report from selected file to Weekly Resource Report.
Sheets("BRT Report").Select
Range("A4:BB3000").Select
Selection.Copy
Windows("Weekly Resource Report.xls").Activate
Sheets("BRT Report").Select
Range("A4").Select
ActiveSheet.Paste
' Copies No of Wkdays Calc from selected file to Weekly Resource Report.
Workbooks.Open Filename:=sWb
Sheets("No of Wkdays Calc").Select
Range("A1:BB3000").Select
Selection.Copy
Windows("Weekly Resource Report.xls").Activate
Sheets("No of Wkdays Calc").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' Updates the Pivot table Cache and data being reported in the Weekly Resource Report.
Sheets("Pivot by Division Detail").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Sheets("Pivot by Division").Select
Range("C13").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Sheets("Pivot by Region").Select
Range("D8").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
' Return to instruction tab
Sheets("Instructions").Select
Range("A1").Select
Exit Sub
err_handler:
MsgBox "No selection made"
End Sub
Bookmarks