refreshing pivot tables with a macro
Rightclick on the pivottable|Table Options
Bottom right corner has an option for background Query. Uncheck it.
Or get them all with a macro:
Option Explicit
Sub testme()
Dim wks As Worksheet
Dim PT As PivotTable
For Each wks In ActiveWorkbook.Worksheets
For Each PT In wks.PivotTables
PT.PivotCache.BackgroundQuery = False
Next PT
Next wks
End Sub
Lou Sanderson wrote:
Background refresh is set to true for the query the pvt table is based off of
- didnt see such an option for the pvt table itself. Its strange that the
.refreshall is not working for me b/c most of the posts on this topic say to
do that...
"Dave Peterson" wrote:
From VBA's help for .refreshall:
Remarks
Objects that have the BackgroundQuery property set to True are refreshed in the
background.
Maybe you want to change property to true.
Lou Sanderson wrote:
Windows XP, Excel 2002 SP 3
I have a simple macro that I run to refreshs all my external datasources in
my spreadsheet. Recently I've added some pivot tables as well. I would like
my macro to refresh the pivot tables if possible - currently it does not. Any
suggestions?
Here is my macro
sub macro1()
application.displayalerts = false
sheets("sheet1").select
range("a1").select
activeworkbook.refreshall
application.displayalerts = true
msgbox("refresh complete")
end sub
Thanks,
Lou Sanderson
--
Dave Peterson
--
Dave Peterson
|