Auto refresh pivot table

T

Tom Nichol

I am using Excel 2000.
I have a pivot table that summarizes data from a database on a separate sheet.
When I delete rows from the database sheet, I must click the refresh button
on the Pivot Table before it will recognize those deletions.
Is there a way to have the pivot table automatically refresh the instant the
rows are deleted?
 
D

Dave Peterson

Even if you could, I'm not sure that's a good idea.

I figure that MS made refreshing the pivottables a manual effort to keep the
time between worksheet changes at a minimum.

Imagine lots of pivottables and each one trying to recalculate each time you
change a value in the pivottable range.

But maybe you could refresh it when you select the worksheet that contains the
pivottable (is it on a different worksheet???)

rightclick on the worksheet tab (with the pivottable) and click on select code.
Then paste this in:

Option Explicit
Private Sub Worksheet_Activate()

Dim myPT As PivotTable

For Each myPT In Me.PivotTables
myPT.RefreshTable
Next myPT

End Sub

If you have lots of pivottables in different sheets, you can get them all with a
line like:

ThisWorkbook.RefreshAll
 
D

Dave Peterson

One spot you could use is under the ThisWorkbook module:

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Me.RefreshAll
End Sub

And it'll update them when you change any sheet.

But you could take that other code and just update the newly activated sheet.
I'm not sure why you want to refresh, but if it's only for looking:

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim myPT As PivotTable
For Each myPT In Sh.PivotTables
myPT.RefreshTable
Next myPT
End Sub


If you have macros that depend on the values being the most current they can be,
you could add that .refreshall line to your macro.
 
D

Dave Peterson

I'm guessing that you're just changing windows--not changing worksheets within
either window.

If you do change worksheets within a window, it does work, right????

I put two procedures in the ThisWorkbook module:

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Me.RefreshAll
End Sub
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Me.RefreshAll
End Sub

Now if I change sheets within a window the top fires. If I change windows, the
bottom fires.
 
T

Tom Nichol

Dave,

Thanks again, very much.
The script works just fine with separate windows.
You do have to activate the window with the pivot table in it;
but that isn't a big deal.

Have a great weekend!

Tom
 
D

Dave Peterson

I didn't need to in xl2002.

Tom said:
Dave,

Thanks again, very much.
The script works just fine with separate windows.
You do have to activate the window with the pivot table in it;
but that isn't a big deal.

Have a great weekend!

Tom
Click to expand...
 
E

Eleanor M

I noticed your informative response and would appreciate your response on a
little problem I have with the Excel worksheet all of a sudden. When I
select refresh pivot table after I made changes to the data worksheet, the
pivot table goes blank it only shows the first column with the dates. This
has worked thousands of time before but not today.

Please advise.

Best Regards,
EMoorhead
 
D

Dave Peterson

Just a complete guess...

Did you change the labels in the raw data?

If the field names get changed, this could cause what you're seeing.
 
E

Eleanor M

No I haven't I was doing the usual routine by adding data at the bottom of
the last input.
 
L

Lori

Dave,

This code worked wonderfully until I edited a column name in the data
source. Now the pivot table no longer refreshes automatically. Any
suggestions as to how I can get it working again?

Thanks,
Lori
 
D

Dave Peterson

It sounds like you may need to recreate the pivottable.

Then test to see if it works.
 
Top