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

  • Thread starter Thread starter billshat44
  • Start date Start date
B

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
 
Try using the Worksheet_Activate event to update the PivotTable like:

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

hth,
jay
 
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.
 
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
 
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
 
Back
Top