|
|
|
|
|
|
|
|
|
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()
Sheets("Pivot").PivotTables("PivotTable1").RefreshTable
End Sub
|
|
How to use:
|
- Open your Excel file.
- Right-click the sheet tab on which the pivot table resides and hit View Code.
- Paste the code into the code window at right.
- Edit the code as described and as necessary.
- Hit the Save button. Close the Visual Basic Editor window.
|
|
Test the code:
|
- Change some data on which your pivot table is based.
- Click on the sheet with your pivot table.
- Pivot table should be adjusted to update any new information.
|
|
Sample File:
|
pivot2.zip 28.92KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 322 times.
|
|
|