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

5 answers

Sort by: Oldest
Most helpful Newest Oldest
  1. HansV 460.5K 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
  2. 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
  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  4. 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
  5. HansV 460.5K 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