pivotable excel2000 macro

G

Gary Brown

Here's a macro for refreshing all pivot tables in a workbook. Hope this
helps.
Gary Brown

Sub PTRefresh()
'Refresh all pivot tables on this worksheet one at a time
'
Dim iSheets As Long, x As Long
Dim iPivot As Long, 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
 
T

Tom Ogilvy

This one Refreshes all external data ranges and PivotTables in the workbook.

Thisworkbook.RefreshAll
 
C

chaim rozent

thank you for the quick answare
but i want only to chack, and not to refresh.
i have to make some operations before the refreshing
so the macro must identify if the pifotable was refreshed
or not
can you help me to build such macro?

thank you
rozent


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top