Automatically update pivot table when switching to sheet that thepivot table is in

[billshat44]

What would the code be to automatically refresh a pivot table when you
open the sheet that the pivot table is in? Today, I changed data in
the sheet that the pivot table draws from and forgot to refresh the
pivot table. Fortunately, I caught my error in time and refreshed the
table before I gave it to the boss. I would like the pivot table to
refresh whenever I switch to the sheet that the pivot table is in.

Thanks

 

[Jay Emory]

Try using the Worksheet_Activate event to update the PivotTable like:

Private Sub Worksheet_Activate()
PivotTables("PivotTable1").RefreshTable
End Sub

hth,
jay

 

[Shane Devenshire]

Hi,

FYI - you can automatically refresh a pivot table when you open a file,
regardless of the sheet by selecting the pivot table and choosing Pivot
Table, Table Options, and checking Refresh on open. This option applies on a
pivot table by pivot table basis, it is not an Excel wide default.

 

[billshat44]

Thank you, this was exactly what I was looking for. When I opened up
the code sheet, there was already code as shown below: Can you tell
me what this code is doing? Do I need to leave it there or can I
delete it.

Thank you very much

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End

 

[Jay Emory]

It looks as though someone was trying to use the Worksheet_SelectionChange
event to run some procedure but never inserted any code. So what you see
runs 'nothing' everytime you move the curser. I would delete both lines.

hth,
jay
Thank you, this was exactly what I was looking for. When I opened up
the code sheet, there was already code as shown below: Can you tell
me what this code is doing? Do I need to leave it there or can I
delete it.

Thank you very much

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End