How do I get a Pivot Table to automatically refresh when the data source (in
the spreedsheet) is refresh.

I am refreshing the data source from the value of a cell on the same sheet
if that helps?

Tia

Jonathan

You could use the change of the cell to trigger a refresh. The code below
should give you a start.

It uses the worksheet_Change event, checking that the cell that changes is
A2. If it is, it switches events off as the refresh will set another
firing, it refreshes a pivot table called PivotTable2 and then switches
events back on. make sure you switch this back on, probably by adding error
checking to do this. If the refresh is successful it returns True. (You may
not need this though)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A2")) Is Nothing Then
Application.EnableEvents = False
Me.PivotTables("PivotTable2").RefreshTable
End If
Application.EnableEvents = True
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Jonathan" wrote in message
...
How do I get a Pivot Table to automatically refresh when the data source
(in
the spreedsheet) is refresh.

I am refreshing the data source from the value of a cell on the same sheet
if that helps?

Tia

Hi Nick,

Thanks for the info but I think I didn't explain myself properly I don't
want the Pivot Table to refresh based on the value of a cell I want the pivot
table to refresh itself autmatically after the ODBC has been refreshed, does
that make more sense?

Regards


Jonathan

"Nick Hodge" wrote:

Jonathan

You could use the change of the cell to trigger a refresh. The code below
should give you a start.

It uses the worksheet_Change event, checking that the cell that changes is
A2. If it is, it switches events off as the refresh will set another
firing, it refreshes a pivot table called PivotTable2 and then switches
events back on. make sure you switch this back on, probably by adding error
checking to do this. If the refresh is successful it returns True. (You may
not need this though)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A2")) Is Nothing Then
Application.EnableEvents = False
Me.PivotTables("PivotTable2").RefreshTable
End If
Application.EnableEvents = True
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Jonathan" wrote in message
...
How do I get a Pivot Table to automatically refresh when the data source
(in
the spreedsheet) is refresh.

I am refreshing the data source from the value of a cell on the same sheet
if that helps?

Tia