Refresh All Pivot Tables on All Worksheets (Excel 2002)

Topic

New Reply

Hi,
I’m trying to get the macro below to refresh all the pivot tables in the workbook upon exiting the “Data” worksheet. It works kind of works, but is NOT refreshing one of the pivot tables on one of the worksheets. It seems to only refresh the pivot table located in the worksheet I click on directly upon exiting the “Data” worksheet (actually it seems to refresh 3 pivot tables on 3 different worksheets, however it skips one pivot table, which is also on it’s own worksheet). I’ve also checked, and all 4 of the pivot tables are named PivotTable1. I’m assuming it’s having a problem because of the “activesheet” part of the macro?? Help!?!?!
Thanks!
Lana

Private Sub Worksheet_Deactivate()
Dim wsh As Worksheet
Dim lngRow As Long

‘Clear zeros out of the summary lines & detail lines in the Data worksheet upon exiting the Data worksheet
With Worksheets(“Data”)
For lngRow = 1 To .Range(“A65536”).End(xlUp).Row
If UCase(.Range(“A” & lngRow)) = “S” Then
.Range(“R” & lngRow & “:Z” & lngRow).ClearContents
.Range(“AH” & lngRow & “:AT” & lngRow).ClearContents
End If
If UCase(.Range(“A” & lngRow)) = “D” Then
.Range(“AA” & lngRow & “:AE” & lngRow).ClearContents
End If
Next lngRow
End With

‘Refresh all pivot tables in every worksheet in the workbook upon exiting the Data worksheet
For Each wsh In Worksheets
ActiveSheet.PivotTables(“PivotTable1”).PivotCache.refresh
Next wsh

End Sub

Reply | Quote

Replies

try this snippet:

For Each wsh In Worksheets
For Each piv In wsh.PivotTables
piv.PivotCache.Refresh
Next piv
Next wsh

Aloha, JohnJ

Reply | Quote

Thank you John… this worked… and thanks to Hans as well for the explanation. I am learning Hans, as I was smart enough to know I had to define the pvt before it would work! Yeah!! I was thinking of the for each function with the pivot tables too, but wasn’t quite sure how to write it. Thanks again to both of you!
Lana

Reply | Quote

John Jacobson has already posted the correct code. It was indeed the use of ‘ActiveSheet’ that caused the problem: ActiveSheet, as the name indicates, always refers to the currently active sheet. Since you have a loop

For Each wsh In Worksheets

Next wsh

you should refer to wsh instead of to ActiveSheet within the loop.

Reply | Quote

Thanks Hans. And the nested loop catches all pivot tables on each sheet, in case there are more than one.
Aloha, JohnJ

Reply | Quote

Yes, that’s an excellent addition.

Reply | Quote

So why not use

ActiveWorkbook.RefreshAll

zeddy

Reply | Quote

What I really meant was

ThisWorkbook.RefreshAll

zeddy

Reply | Quote