VBA Pivot Code Erroring Out...quick help?

[nick612hayden]

Can you direct me as to why?

Would be much appreciated!
Nick

Sub ALL()
Dim wBook As Workbook
Dim wSheet As Worksheet
Dim Pwd As String

Pwd = "password"

Application.ScreenUpdating = False

For Each wBook In Workbooks

'Unprotect all sheets at once
For Each wSheet In wBook.Worksheets
wSheet.Unprotect Password:=Pwd
Next wSheet

'Refresh all pivot tables
For Each wSheet In wBook.Worksheets
Sheets("Sheet1").Select
ActiveSheet.PivotTables("PivotTable4").RefreshTable
ActiveSheet.PivotTables("PivotTable5").RefreshTable
Sheets("Sheet2").Select
ActiveSheet.PivotTables("PivotTable1").RefreshTable
Sheets("Sheet3").Select
ActiveSheet.PivotTables("PivotTable3").RefreshTable

'Protect all sheets at once
For Each wSheet In wBook.Worksheets
wSheet.Protect Password:=Pwd, AllowFiltering:=True, _
AllowFormattingColumns:=False, AllowFormattingRows:=True, AllowUsingPivotTables:=True
Next wSheet

Next wBook

Application.ScreenUpdating = True

End Sub

 

[nick612hayden]

Forgot to say that the error is the part where the code should be updating each pivot. It works if I manually click RefreshAll in the ribbon, but the macro wBook.RefreshAll makes it that when I try to collapse/expand field, it returns Pivot Table Saved without Underlying Data.

Specifically, the part where it is the 'Refresh all pivot tables. I am trying to specify each pivot name in each sheet (which is the same in all open workbooks) so that it works

 

[Jerry Sullivan]

Hi Nick,

Does your code stop with a run-time error? or does it refresh all the data, but not save the underlying data?

Have you checked to see that the PivotTable Option is checked to save underlying data before running the macro?

Based on your response, I can provide some alternative code which updates each PivotCache in the workbook instead of each PivotTable.

 

[nick612hayden]

It appears to refresh all the data, but not save the underlying data; yes. The ony two things I have unchecked in the Data option is Retained Items Deleted from Data Source and Refresh Data When Opening File...don't see an option to save underlying data before running macro (Excel for Mac2011) :/

The alt code might be the best bet...when you have the moment. Thanks!

 

[Jerry Sullivan]

Nick, Sorry for my confusing poor grammar. What I meant is...

Before running the macro during your testing, check to see that the PivotTable Option: "Save source data with file" is checked.

It sounds like you do have that option checked and that isn't the issue.

Looking at the code, there are some problems that might have left the pivots in a state that caused the grouping (expand/collapse) not to work correctly.

This part of the code is missing a Next statement which should have prevented it from compiling. (maybe a typo in posting the code to the forum).

Even if that were added, the code has an extra nested loop in that with each wSheet, it repeats the process of updating all 3 sheets.

For Each wSheet In wBook.Worksheets
     Sheets("Sheet1").Select
     ActiveSheet.PivotTables("PivotTable4").RefreshTable
     ActiveSheet.PivotTables("PivotTable5").RefreshTable
     Sheets("Sheet2").Select
     ActiveSheet.PivotTables("PivotTable1").RefreshTable
     Sheets("Sheet3").Select
     ActiveSheet.PivotTables("PivotTable3").RefreshTable
[COLOR="#FF0000"]Next wSheet  'missing[/COLOR]
'Protect all sheets at once
 For Each wSheet In wBook.Worksheets

Here's some alternative code. Hopefully your grouping functionality will be preserved after running it.

Sub Refresh_All_Pivots()
    Dim wBook As Workbook
    Dim wSheet As Worksheet
    Dim Pwd As String
    Dim PC As PivotCache
    

    Pwd = "password"
 
    Application.ScreenUpdating = False
 

    For Each wBook In Workbooks
         
        'Unprotect all sheets at once
        For Each wSheet In wBook.Worksheets
            wSheet.Unprotect Password:=Pwd
        Next wSheet
         
        'Refresh all pivot caches
        For Each PC In wBook.PivotCaches
            PC.Refresh
        Next PC
         
        'Protect all sheets at once
        For Each wSheet In wBook.Worksheets
            wSheet.Protect Password:=Pwd, AllowFiltering:=True, _
            AllowFormattingColumns:=False, AllowFormattingRows:=True, _
            AllowUsingPivotTables:=True
        Next wSheet
 
        
    Next wBook
     
    Application.ScreenUpdating = True
End Sub

 

[nick612hayden]

Jerry

Worked like a charm...glad I don't have to go through those 3steps, for each Workbook, 50 times every month

Thanks much!