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


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
 
Ad

Advertisements

J

Jay Emory

Try using the Worksheet_Activate event to update the PivotTable like:

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

hth,
jay
 
S

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.
 
B

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
 
Ad

Advertisements

J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top