Macro To Update Pivot Tables on Several Worksheets

  • Thread starter Thread starter Gringarlow
  • Start date Start date
G

Gringarlow

I have less than a little knowledge of Macros and VBA.
I know how to create a Macro that follows my Mouse clicks etc.......
Can someone show me a Macro to update several Pivot Tables on several
Worksheets in the same work book. The tables all use the same data. I'm using
Excel 2003 SP3
Thank, You too much
 
I have less than a little knowledge of Macros and VBA.
I know how to create a Macro that follows my Mouse clicks etc.......
Can someone show me a Macro to update several Pivot Tables on several
Worksheets in the same work book. The tables all use the same data. I'm using
Excel 2003 SP3
Thank, You too much
Click to expand...

Gringarlow,

There is some sample code below for updating pivot tables through the
worksheets within the active workbook.

Best,

Matthew Herbert

Sub PivotTableRefresh()
Dim Wks As Worksheet
Dim pvtTable As PivotTable

'don't allow screen updating because it takes time
Application.ScreenUpdating = False

'loop through each worksheet
For Each Wks In ActiveWorkbook.Worksheets

'loop through each pivot table in the worksheet
For Each pvtTable In Wks.PivotTables

'refresh the pivot table
pvtTable.PivotCache.Refresh

Next pvtTable

Next Wks

'tell the user you are done with the refresh
MsgBox "The pivot tables have been updated."

End Sub
 
Matthew,

I added a "ActiveWorkbook.RefreshAll" to the front of your code & in only
refreshes the data sheet, leaving the pivot tables untouched (see below).
I'm trying 1st refresh the data sheets connected to an outside database.
Then refresh all of the end user's custom pivot tables. I'd also like to add
a refresh date to all the sheets in the workbook in (Z1).

Private Sub CmdRefreshAll_Click()
'Refresh data sheets for outside data sources
ActiveWorkbook.RefreshAll
MsgBox "All Data Sheets are updated, click ok to update Pivot tables"

'Refresh Pivot tables
Dim Wks As Worksheet
Dim pvtTable As PivotTable

Application.ScreenUpdating = False

For Each Wks In ActiveWorkbook.Worksheets

For Each pvtTable In Wks.PivotTables

pvtTable.PivotCache.Refresh

Next pvtTable

Next Wks

MsgBox "All Data Sheets & Pivot Tables are updated"

End Sub
 
Back
Top