How to refresh Pivot table on protected sheet?

Anonymous
2014-09-20T10:24:51+00:00

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

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
HansV 462.7K Reputation points MVP Volunteer Moderator
2014-09-20T15:58:43+00:00

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.

Was this answer helpful?

4 people found this answer helpful.
0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.7K Reputation points MVP Volunteer Moderator
    2014-09-22T18:55:35+00:00

    You can copy the macro into a standard module (the kind of module that you create by selecting Insert > Module in the Visual Basic Editor).

    You can execute it in different ways:

    • Press Alt+F8 to activate the Macros dialog. Select RefreshPT, then click Run.
    • Assign a keyboard shortcut to the macro: press Alt+F8 to activate the Macros dialog. Select RefreshPT, then click Options. Enter a lower-case or upper-case letter or a digit in the Shortcut key box, then click OK.
    • Assign the macro to a Quick Access Toolbar button.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2014-09-22T17:22:19+00:00

    Thanks for your inputs HansV.

    Can you please confirm whether I have to insert this code in all the sheets?

    and

    After keeping this code, how will this get executed, whether by the regular way of executing the 'Refresh All' command on task bar?

    Regards,

    Krishna

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-09-20T14:21:09+00:00

    Dear HansV,

    Thanks for your advise, but will this work if the sheet is password protected?

    and can you please advise how can we place a button to refresh all the pivots in the workbook at once.

    Regards,

    Krishna

    Was this answer helpful?

    0 comments
  4. HansV 462.7K Reputation points MVP Volunteer Moderator
    2014-09-20T10:48:08+00:00

    You could place a command button on the sheet that runs the following macro:

    Sub RefreshPT()

        ActiveSheet.PivotTables(1).PivotCache.Refresh

    End Sub

    Users will have to enable macros, of course.

    Was this answer helpful?

    0 comments