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.
I ran across the following code on EE:
Sub RefreshPivotTableData()
' Browses all the worksheets and refreshes all the pivot tables
Application.ScreenUpdating
For Each sht In Worksheets
For Each pt In sht.PivotTables
pt.RefreshTable
Next pt
Next sht
Application.ScreenUpdating
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.
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
The refresh button only refreshes the current pivot table. Refreshall does all pivots and external queries.
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?
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?
No - the code goes once in the thisworkbook module and nowhere else.
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.
Sorry about that. Thanks for that great solution.
Open in new window
or just use the refreshall button. ;)