Auto refresh pivot table
 

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

"Dave Peterson" wrote:

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

Tom Nichol wrote:

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?

--

Dave Peterson

Auto refresh pivot table
 

It sounds like you may need to recreate the pivottable.

Then test to see if it works.

Lori wrote:

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

"Dave Peterson" wrote:

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

Tom Nichol wrote:

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?

--

Dave Peterson



--

Dave Peterson