How to refresh Pivot table on protected sheet?

Hello all,

I have created a Pivot table to summarize the data entered by my team members on a different sheet.

I want to give only view or print access to the Pivot table sheet for my team. If I protect the sheet with a password, an error message is getting displayed that the Pivot tables on protected sheet cannot be refreshed. Kindly advise how I can I overcome this issue.

Thanks a lot for your kind advise.

Regards,

Krishna

Answer
Answer

Sorry, I forgot to add the code to unprotect/protect the sheet.

If all sheets in your workbook are protected with the same password, you can use the following:

Sub RefreshPT()
    Dim wsh As Worksheet
    Dim pvt As PivotTable
    For Each wsh In Worksheets
        wsh.Unprotect Password:="secret"
        For Each pvt In wsh.PivotTables
            pvt.PivotCache.Refresh
        Next pvt
        wsh.Protect Password:="secret"
    Next wsh
End Sub

where "secret" is the password.

---
Kind regards, HansV
https://www.eileenslounge.com

4 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated February 18, 2021 Views 6,797 Applies to: