Link to home
Start Free TrialLog in
Avatar of witzph1
witzph1

asked on

Automatic Refresh of Pivot Table Data

I am trying to create a macro to automatically refresh all data in all Pivot Tables of all my worksheets.  (This particular worbook has upwards of 90 worksheets, each with a Pivot Table.)  

I ran across the following code on EE:

Sub RefreshPivotTableData()
' Browses all the worksheets and refreshes all the pivot tables


Application.ScreenUpdating = False
 
For Each sht In Worksheets
    For Each pt In sht.PivotTables
 
            pt.RefreshTable
           
    Next pt
Next sht
 
Application.ScreenUpdating = True
 
End Sub

But when it runs it gives me a "compile error: variable not defined," and it's highlighting the "scr" variable.  (See attached screen shot.)

I'm no programmer, so any help would be greatly appreciated. User generated image
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Sub RefreshPivotTableData()
' Browses all the worksheets and refreshes all the pivot tables
Dim sht as Worksheet, PT as PivotTable

Application.ScreenUpdating = False
 
For Each sht In Worksheets
    For Each pt In sht.PivotTables
 
            pt.RefreshTable
            
    Next pt
Next sht
 
Application.ScreenUpdating = True
 
End Sub

Open in new window


or just use the refreshall button. ;)
Avatar of witzph1
witzph1

ASKER

I copied and pasted the code.  Was what I copied specific to a different versison of Excel or something?  

By the way, does the refresh data button do all sheets, or just the one I'm on.  Maybe you are right, I should just use that button if it does them all.

To be honest, I would really love for the person who will be using this workbook to have the data refreshed as they click on one of the tabs.  For instance, they will start typically by adding lines to the main log page.  Then, when they click on any one of the 90 some tabs containing Pivot Tables which summarize the main page by the location column, I would like the data to be refreshed.  I'm afraid they are going to forget to refresh and miss data in the reports.

Should I be looking at something different entirely?
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of witzph1

ASKER

User generated image
Is the "RefreshAll" button something different than the "Refresh Data" button on my PivotTable toolbar? (See screen shot.)  

Is the 6 lines of new code something I should add in addition to, or instead of the other?  And is this new code something that automatically executes when clicking on a tab?  Sorry to be so dense on this.
The code replaces what you have and runs automatically as you switch tabs.
The refresh button only refreshes the current pivot table. Refreshall does all pivots and external queries.
Avatar of witzph1

ASKER

Okay.  That sounds like it should be much simpler.  Plus, if when clicking the tab, it only refreshes the data on that tab, that's probably better.  With 90 tabs, a refresh all might bog down too much.

But I did put the code in each sheet object and it doesn't seem to be triggering a refresh when I click on the tabs.  Does it look like I put it in the right place? User generated image
No - the code goes once in the thisworkbook module and nowhere else.
Avatar of witzph1

ASKER

Okay, that last one was a really stupid question.  I was reading ThisWorkbook and thinking in my mind This Worksheet for some reason.  I guess because we had talked about the updating occurring as you click on a worksheet.  

Sorry about that.  Thanks for that great solution.