Works like charm now !
Thanks a lot.

"Dave Peterson" wrote:

> Ahhhhhhhh.
>
> When the pivottables are refreshed, it causes the worksheet_Change event to fire
> again. In my haste, I didn't disable the events (I just copied the code over).
>
> Try this version.
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim myPT As PivotTable
> Application.enableevents = false
> For Each myPT In Me.PivotTables
> myPT.RefreshTable
> Next myPT
> application.enableevents = true
> End Sub
>
>
>
> Luc Poppe wrote:
> >
> > 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
> > >
>
> --
>
> Dave Peterson
>