Excel

Auto Refresh Pivot Table on Activate Worksheet

Ease of Use

Easy

Version tested with

2000 

Submitted by:

Zack Barresse

Description:

Automatically refresh the pivot table on a worksheet when you click on the worksheet's tab or otherwise activate the worksheet. 

Discussion:

You must know the sheet name and the pivot table name. To determine the pivot table's name, right-click a cell of the pivot table and choose 'Table Options...'. You can obtain or even change the pivot table's name there. You can change your data on any sheet, then click on the sheet that contains the pivot table, and it'll automatically refresh the pivot table with the new data. 

Code:

instructions for use

			

Private Sub Worksheet_Activate() 'If this worksheet is activated, refresh the pivot table 'Change "Pivot" to your sheet's name 'Change "PivotTable1" to your pivot table's name Sheets("Pivot").PivotTables("PivotTable1").RefreshTable End Sub

How to use:

  1. Open your Excel file.
  2. Right-click the sheet tab on which the pivot table resides and hit View Code.
  3. Paste the code into the code window at right.
  4. Edit the code as described and as necessary.
  5. Hit the Save button. Close the Visual Basic Editor window.
 

Test the code:

  1. Change some data on which your pivot table is based.
  2. Click on the sheet with your pivot table.
  3. Pivot table should be adjusted to update any new information.
 

Sample File:

pivot2.zip 28.92KB 

Approved by mdmackillop


This entry has been viewed 285 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express