refreshing pivot tables with a macro

[Lou Sanderson]

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]

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.

 

[Gary L Brown]

'/-------------------------------------/
Sub PTRefresh()
'Refresh all pivot tables in this workbook one at a time
'
Dim iSheets As Integer, x As Integer
Dim iPivot As Integer, strCurrentSheet As String

On Error GoTo Exit_PTRefresh

'Count number of sheets in workbook
iSheets = ActiveWorkbook.Sheets.Count

'remember current sheet
strCurrentSheet = ActiveSheet.name

If Windows.Count = 0 Then GoTo Exit_PTRefresh

For x = 1 To iSheets

'go to a worksheet to refresh pivot tables
Sheets(x).Activate

'turn warning messages off
Application.DisplayAlerts = False

'refresh all pivot tables on this worksheet one at a time
For iPivot = 1 To ActiveSheet.PivotTables.Count
ActiveSheet.PivotTables(iPivot).RefreshTable
Next

'turn warning messages on
Application.DisplayAlerts = True

Next

'return to worksheet that you were originally at
Application.ActiveWorkbook.Sheets(strCurrentSheet).Activate

Exit_PTRefresh:
Application.DisplayAlerts = True

End Sub
'/-------------------------------------/

HTH,

 

[Lou Sanderson]

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]

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]

Ok, I see what you mean. That option is greyed out.

The pvt table is based on an external data query on a seperate page - so the
data source for the pvt table is an excel data range, vice external data.

 

[Lou Sanderson]

OK, I'm close now

I slightly modified your code to:

Sub testme()
Dim wks As Worksheet
Dim PT As PivotTable
For Each wks In ActiveWorkbook.Worksheets
For Each PT In wks.PivotTables
PT.refreshtable
Next PT
Next wks
End Sub

and it almost works. Not every tab on my spreadsheet has a pvt table, so
this errors if it comes across a page w/o a pvt table. how can I account for
that?

thanks for your help.

 

[Lou Sanderson]

Gary,

I tried this - didnt get any result. Nothing happened.

Used the debug to step through, and it goes fine up until * (below) and then
jumps from that line to the error handling. I'm not sure why...

 

[Dave Peterson]

I don't have any other suggestions.

Good luck,

Ok, I see what you mean. That option is greyed out.

The pvt table is based on an external data query on a seperate page - so the
data source for the pvt table is an excel data range, vice external data.

 

[Dave Peterson]

When I ran this code against a workbook that had worksheets with no pivottables,
it ran successfully.

OK, I'm close now

I slightly modified your code to:

Sub testme()
Dim wks As Worksheet
Dim PT As PivotTable
For Each wks In ActiveWorkbook.Worksheets
For Each PT In wks.PivotTables
PT.refreshtable
Next PT
Next wks
End Sub

and it almost works. Not every tab on my spreadsheet has a pvt table, so
this errors if it comes across a page w/o a pvt table. how can I account for
that?

thanks for your help.

 

[Lou Sanderson]

Yes, you are right.

I had a problem, but it was something else, the code worked correctly.

Thank you for your help Dave.

-Lou