Using your latest routine does indeed cause the tabel to update. Although
being a very small table (1 column, 8 rows), excel seems to go through a
series of calculations that causes the worksheet to flash for about 2
seconds. Is it recalcualting 8 times, since I have 8 rows ?? or what's going
on ?

"Dave Peterson" wrote:

If the changes are by typing, you could use the worksheet_change event instead:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myPT As PivotTable
For Each myPT In Me.PivotTables
myPT.RefreshTable
Next myPT
End Sub

(Same spot--and you can dump the other procedure.)

But there is a reason that MS designed pivottables to not update when you make
changes--you'll usually suffer a performance hit. If it's a giant pivottable
(or lots of giant pivottables), you may not want to do this.

But if you select a cell in one of the pivottables, the pivottable toolbar will
appear. You can click the refresh icon (Looks like an exclamation point (!)) to
refresh that one.



Luc Poppe wrote:

That seems to work fine, but how can we make it such that is refreshes every
time I update the source data on the same sheet. I want to avoid creating a
blank sheet just to go back and forth, something that not everyone will
remember to do.

"Dave Peterson" wrote:

This event fires when the worksheet is activated.

So click on another worksheet, then come back and select this worksheet.

Luc Poppe wrote:

Debra,
You are referring to the correct thread. I used the following code:

Option Explicit
Private Sub Worksheet_Activate()

Dim myPT As PivotTable

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

End Sub

and pasted it into the worksheet by right-clicking the sheet tab, selecting
"view-code", pasting the code above into the VB code window, click save and
exited VB app. I did not perform any other functions or selections available
in the VB app, as I'm not familiar with this.
Thanks for the reply,
Luc Poppe

"Debra Dalgleish" wrote:

I guess you mean the code that Dave Peterson suggested in this thread:


http://groups.google.ca/group/micros...5c284c4?hl=en&

Which code did you use, and where did you store the code?

Luc Poppe wrote:
I need to auto refresh a pivot table on a worksheet in Excel 2002. Tried to
use suggested macro from Dave Patterson of 8/23/04 in this forum, but it does
not work, it was a macro for excel 2000. I have no experience with Visual
Basic.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html



--

Dave Peterson


--

Dave Peterson