unprotect all sheets, update all pivits then protect all sheets

[GaryHinton]

Good morning folks.
I am in the neeed for a VBA code in Excel to
1 unprotect all sheets
2 update all pivits
3 protect all sheets
Any help is greatly appreciated.
I am planning on adding a button to run this all as well and know how to do this.

I am hoping this can be done without listing all the sheets individually.

 

[DanteAmor]

Try the following.
Change "abc" for the password of your sheets
If they don't have a password then change the lines
sh.Unprotect "abc"
sh.Protect "abc"

to this:
sh.Unprotect
sh.Protect

Sub Macro1()
  Dim sh As Worksheet
  Dim pvt As PivotTable
 
  For Each sh In Sheets
    sh.Unprotect "abc"
    For Each pvt In sh.PivotTables
      pvt.PivotCache.Refresh
    Next
    sh.Protect "abc"
  Next
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[GaryHinton]

nope that didnt seem to work. Still comes up with error cannot update a protected sheet.

 

[DanteAmor]

Did you change the macro?
You can share your book on google drive.

You could upload a copy of your file to a free site such google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

If the workbook contains confidential information, you could replace it with generic data.

​

It is not even necessary for the sheets to have information, I just want to check how you put my macro and if you have other macros that are affecting the execution.

 

[GaryHinton]

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).

drive.google.com

 

[DanteAmor]

error cannot update a protected sheet

Este es el mensaje completo:

The problem is not the sheet that contains the PivotTable, this sheet is Unprotected by the macro, the problem is that the PivotTable contains another PivotTable report based on the same source data.

So what we will do is unprotect all the sheets.
Then update all the pivotables of all the sheets and at the end we protect all the sheets:

Sub Refresh_PivotTables()
  Dim sh As Worksheet
  Dim pvt As PivotTable
  Dim nm As String
 
  For Each sh In Sheets
    sh.Unprotect "ABC"
  Next
 
  For Each sh In Sheets
    For Each pvt In sh.PivotTables
      nm = sh.Name
      pvt.PivotCache.Refresh
    Next
  Next
  
  For Each sh In Sheets
    sh.Protect "ABC"
  Next
End Sub

--------------
The macro works on the file you shared, but try it on your file and let me know.
Cordially
Dante Amor
--------------

 

[GaryHinton]

Thank you so much works great

 

[GaryHinton]

Sub Refresh_PivotTables()
Dim sh As Worksheet
Dim pvt As PivotTable
Dim nm As String

For Each sh In Sheets
sh.Unprotect "ABC"
Next

For Each sh In Sheets
For Each pvt In sh.PivotTables
nm = sh.Name
pvt.PivotCache.Refresh
Next
Next

For Each sh In Sheets
sh.Protect "ABC"
Next
End Sub

Macro works, however now it clears all of the check settings and prevents from using. How can I add this to the macro?

 

[DanteAmor]

Use:

Sub Refresh_PivotTables()
  Dim sh As Worksheet
  Dim pvt As PivotTable
  Dim nm As String
 
  For Each sh In Sheets
    sh.Unprotect "ABC"
  Next
 
  For Each sh In Sheets
    For Each pvt In sh.PivotTables
      nm = sh.Name
      pvt.PivotCache.Refresh
    Next
  Next
  
  For Each sh In Sheets
    sh.Protect "ABC", _
      DrawingObjects:=False, _
      Contents:=True, _
      Scenarios:=False, _
      AllowSorting:=True, _
      AllowFiltering:=True, _
      AllowUsingPivotTables:=True
    sh.EnableSelection = xlUnlockedCells
  Next
End Sub

Regards
Dante Amor