Automatically Refresh pivot table

[Shell]

In Excel 2000, I have a spreadsheet with several table. Some of the tabs
have been filled with data from an Access MDB. I have manually created a
second tab which contains a pivot table based on the data I passed in to the
ther tab.

The source data tab always has a different number of rows every time the
Access MDB is executed.

How can I automatically refresh the pivot table?

Thanks

 

[Jim Thomlinson]

What is your trigger to refresh. When you open the book or every ?? minutes?
or...

There are some options in Table Options. Right click the pivot table and
select Table Options. At the bottom are the refresh options (IIRC for 2000).

 

[Shell]

When I open the pivot table tab
--
Shell

What is your trigger to refresh. When you open the book or every ?? minutes?
or...

There are some options in Table Options. Right click the pivot table and
select Table Options. At the bottom are the refresh options (IIRC for 2000).

 

[Jim Thomlinson]

When you open the workbook is covered. To set it at the selection of the tab
will require macro code. Not hard to do but you need to be open to using
macros.

Right click the sheet tab, Select view code and paste the following in the
code window...

Private Sub Worksheet_Activate()
Dim pvt As PivotTable

For Each pvt In Me.PivotTables
pvt.RefreshTable
Next pvt
End Sub

This will refresh all of the pivot tables on the tab in question.

 

[Shell]

I'm not averse to using macros. I usually write VBA code in Access.

I tried your code and finally it does not crash on an index error. I know
the code is running, I put in a break point, but the pivot table did not
update. I added a line in the data tab with a large numeric value. It did
not show up in the pivot table.

 

[Shell]

The reason the pivot table did not update, even though the code ran, is that
the new data had an extra line in it. The line I added. I need the macro to
account to increases or decreases in the original data (number of rows).

 

[Jim Thomlinson]

I was under the impression that the souce was Access but I guess that is not
the case. The source is actually a data set within the sheet. If the pivot
was hooked to the external data then all would have been well. But as it
stands we need to change the data source from a static range to a dynamic
range. Check out this link on Dynamic named ranges...

http://www.contextures.com/xlPivot01.html

Under Dynamic Data Source...