Run macro on multiple protected sheets

[snichols101]

Hi All

I have the following macro assigned to a macro button which refreshes 2 hidden pivot tables from a protected worksheet and brings back the refreshed data;

Sub Refresh()​

​

' Refresh Macro​

​

Worksheets("Pivot").PivotTables("PivotTable1").PivotCache.Refresh​

Worksheets("Pivot3").PivotTables("PivotTable3").PivotCache.Refresh​

​

End Sub​

The marco works when sheets are unprotected, but fails (Run time error '1004') when sheets are protected. I've tried 'unprotecting' code but with no avail. Can anybody help?

Thanks

 

[Michael M]

Try

Sub Refresh()
Worksheets("Pivot").Unprotect Password:="your Passowrd"
Worksheets("Pivot3").Unprotect Password:="your Passowrd"
Worksheets("Pivot").PivotTables("PivotTable1").PivotCache.Refresh
Worksheets("Pivot3").PivotTables("PivotTable3").PivotCache.Refresh
Worksheets("Pivot").Protect Password:="your Passowrd"
Worksheets("Pivot3").Protect Password:="your Passowrd"

End Sub

 

[snichols101]

Brilliant! Thank you so much.
Cheers

 

[Michael M]

glad to help...