Share via

How do I create a Refresh Button on my pivot table

Anonymous
2013-09-19T16:05:01+00:00

I am creating a pivot table that other people are going to be using at work.  Some of them are not very computer literate so I'm trying to make it as easy as possible.  I know I can refresh the data in my pivot table by going to Pivot Table Tools (once I'm on the pivot table)  and going to Refresh, but how can I create a Button actually on my spreadsheet beside my pivot table that they just need to click on to refresh the data.  I've seen this before, but I can't seem to find out how to do it anywhere.  Any help is greatly appreciated.    Thank you:)  Michelle

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

  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2013-09-19T16:49:02+00:00

    Click anywhere in the pivottable.

    Activate the Developer tab of the ribbon.

    In the Controls group, click Insert > Button under Form Controls.

    Click on the worksheet or drag a rectangle on the worksheet.

    The Assign Macro dialog appears.

    Click New

    The Record Macro dialog appears.

    You can assign a keyboard shortcut here if you wish.

    Click OK.

    The Visual Basic Editor appears, with code similar to

    Sub Button1_Click()

    End Sub

    Edit it to make it look like this:

    Sub Button1_Click()

        ActiveSheet.PivotTables(1).PivotCache.Refresh

    End Sub

    Switch back to Excel.

    Save the workbook in a macro-enabled format such as .xlsm, .xlsb or .xls.

    60+ people found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2016-12-05T16:50:55+00:00

    What is the 'It' in 'It always refers to the "Button1_Click()" part'?

    4 people found this answer helpful.
    0 comments
  2. Anonymous
    2016-12-05T16:41:30+00:00

    Thanks, but it doesn't work for me. It always refers to the "Button1_Click()" part.

    0 comments
  3. HansV 462.6K Reputation points MVP Volunteer Moderator
    2015-08-27T21:03:12+00:00
    1. Yes, you can create multiple pivot tables on the same worksheet.
    2. If two pivot tables share the same data source, updating one of them will automatically update the other, unless you follow a series of steps to break the link between them.
    3. If two pivot tables have different data sources, they need to be updated separately.

    With the same setup as in my previous reply, the code for a second button to update a second pivot table would look like this:

    Sub Button2_Click()

        ActiveSheet.PivotTables(2).PivotCache.Refresh

    End Sub

    0 comments
  4. Anonymous
    2015-08-27T20:18:19+00:00

    Click anywhere in the pivottable.

    Activate the Developer tab of the ribbon.

    In the Controls group, click Insert > Button under Form Controls.

    Click on the worksheet or drag a rectangle on the worksheet.

    The Assign Macro dialog appears.

    Click New

    The Record Macro dialog appears.

    You can assign a keyboard shortcut here if you wish.

    Click OK.

    The Visual Basic Editor appears, with code similar to

    Sub Button1_Click()

    End Sub

    Edit it to make it look like this:

    Sub Button1_Click()

        ActiveSheet.PivotTables(1).PivotCache.Refresh

    End Sub

    Switch back to Excel.

    Save the workbook in a macro-enabled format such as .xlsm, .xlsb or .xls.

    I am new to pivot tables and the instructions above were great!!  Can two pivot tables exist on one worksheet?  Can each have individual refresh buttons?  Any idea of the code for that?

    0 comments