Pivot Table Refresh Problems

Hi All:
Hoping that someone can help. I am trying to refresh a pivot table
using the following code which was inserted on the Daily Production
Output Sheet(both sheets in the same workbook). I am using Excel 2003
Private Sub Worksheet_Calculate()


'If data on this worksheet changes, refresh the pivot table
Sheets("Daily Production
Output").PivotTables("PivotTable3").RefreshTable


End Sub


The Calculation has been set to Automatic.


However when I try to run the above code I am getting the following
error" Run Time error 1044, Application defined or Object defined
error". I think that the code worked a couple days ago.
PFL


What can I do to run the code ?
Thanks in advance

My first guess is that the pivottable isn't named "pivottable3" (anymore).

If you rightclick on the pivottable and choose "table options" what do you see
in the "name" box?

If that's the only PT on that sheet, you could do:

Worksheets("daily production output").PivotTables(1).RefreshTable

(and never have to worry about the name again.)



PFL wrote:
>
> Hi All:
> Hoping that someone can help. I am trying to refresh a pivot table
> using the following code which was inserted on the Daily Production
> Output Sheet(both sheets in the same workbook). I am using Excel 2003
> Private Sub Worksheet_Calculate()
>
> 'If data on this worksheet changes, refresh the pivot table
> Sheets("Daily Production
> Output").PivotTables("PivotTable3").RefreshTable
>
> End Sub
>
> The Calculation has been set to Automatic.
>
> However when I try to run the above code I am getting the following
> error" Run Time error 1044, Application defined or Object defined
> error". I think that the code worked a couple days ago.
> PFL
>
> What can I do to run the code ?
> Thanks in advance

--

Dave Peterson

And if you have multiple pivottables on that sheet, you could do:

Dim PT As PivotTable
For Each PT In Worksheets("Daily Production Output").PivotTables
PT.RefreshTable
Next PT

or even get all the PT's in the whole workbook.

Me.Parent.RefreshAll

You may want to add

application.enableevents = false
'your code here
application.enableevents = true

If there's a chance that the calculation event could be called recursively.






PFL wrote:
>
> Hi All:
> Hoping that someone can help. I am trying to refresh a pivot table
> using the following code which was inserted on the Daily Production
> Output Sheet(both sheets in the same workbook). I am using Excel 2003
> Private Sub Worksheet_Calculate()
>
> 'If data on this worksheet changes, refresh the pivot table
> Sheets("Daily Production
> Output").PivotTables("PivotTable3").RefreshTable
>
> End Sub
>
> The Calculation has been set to Automatic.
>
> However when I try to run the above code I am getting the following
> error" Run Time error 1044, Application defined or Object defined
> error". I think that the code worked a couple days ago.
> PFL
>
> What can I do to run the code ?
> Thanks in advance

--

Dave Peterson