Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Refresh All Pivot Tables on All Worksheets (Excel 2002)

    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

  2. #2
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Refresh All Pivot Tables on All Worksheets (Excel 2002)

    try this snippet:

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

    Aloha, JohnJ

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Refresh All Pivot Tables on All Worksheets (Excel 2002)

    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.

  4. #4
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Refresh All Pivot Tables on All Worksheets (Excel 2002)

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

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Refresh All Pivot Tables on All Worksheets (Excel 2002)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Refresh All Pivot Tables on All Worksheets (Excel 2002)

    Yes, that's an excellent addition. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Refresh All Pivot Tables on All Worksheets (Excel 2002)

    So why not use

    ActiveWorkbook.RefreshAll


    zeddy

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Refresh All Pivot Tables on All Worksheets (Excel 2002)

    What I really meant was

    ThisWorkbook.RefreshAll

    zeddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •