Hi
I got tired of keep create the new pivot table every time there're new data
in the sheet. Therefore, I use the following code in the macro to update but
it keeps giving an error '!UpdateIt' cannot be found ??? ~ PLS HELP !

Sub Auto_Open()
Application.OnSheetActivate = "UpdateIt"
End Sub

Sub UpdateIt()
Dim iP As Integer
Application.DisplayAlerts = False
For iP = 1 To ActiveSheet.PivotTables.Count
ActiveSheet.PivotTables(iP).RefreshTable
Next
Application.DisplayAlerts = True
End Sub

Thanks,
Excel Rookie

Why not just call it

Sub mytest()
Call UpdateIt
End Sub

"Excel Rookie" wrote:

> Hi
> I got tired of keep create the new pivot table every time there're new data
> in the sheet. Therefore, I use the following code in the macro to update but
> it keeps giving an error '!UpdateIt' cannot be found ??? ~ PLS HELP !
>
> Sub Auto_Open()
> Application.OnSheetActivate = "UpdateIt"
> End Sub
>
> Sub UpdateIt()
> Dim iP As Integer
> Application.DisplayAlerts = False
> For iP = 1 To ActiveSheet.PivotTables.Count
> ActiveSheet.PivotTables(iP).RefreshTable
> Next
> Application.DisplayAlerts = True
> End Sub
>
> Thanks,
> Excel Rookie

Where is the Updateit macro located. Is it in a general module
(insert=>Module) in the VBE?

with both subs in a general module, it worked fine for me. The code ran
whenever I selected a different sheet.

--
Regards,
Tom Ogilvy



"Excel Rookie" <Excel (E-Mail Removed)> wrote in message
news:28898F18-27F0-412E-A8D6-(E-Mail Removed)...
> Hi
> I got tired of keep create the new pivot table every time there're new
> data
> in the sheet. Therefore, I use the following code in the macro to update
> but
> it keeps giving an error '!UpdateIt' cannot be found ??? ~ PLS HELP !
>
> Sub Auto_Open()
> Application.OnSheetActivate = "UpdateIt"
> End Sub
>
> Sub UpdateIt()
> Dim iP As Integer
> Application.DisplayAlerts = False
> For iP = 1 To ActiveSheet.PivotTables.Count
> ActiveSheet.PivotTables(iP).RefreshTable
> Next
> Application.DisplayAlerts = True
> End Sub
>
> Thanks,
> Excel Rookie

Thanks Joel & Tom. It works now. I changed the name of the sub procedure.

"Excel Rookie" wrote:

> Hi
> I got tired of keep create the new pivot table every time there're new data
> in the sheet. Therefore, I use the following code in the macro to update but
> it keeps giving an error '!UpdateIt' cannot be found ??? ~ PLS HELP !
>
> Sub Auto_Open()
> Application.OnSheetActivate = "UpdateIt"
> End Sub
>
> Sub UpdateIt()
> Dim iP As Integer
> Application.DisplayAlerts = False
> For iP = 1 To ActiveSheet.PivotTables.Count
> ActiveSheet.PivotTables(iP).RefreshTable
> Next
> Application.DisplayAlerts = True
> End Sub
>
> Thanks,
> Excel Rookie

I am tring to do a similar thing can you help?
I amrunnig a SQL querry in excel based on the now() function and - 24H.
My SQL querry updates just fine but my pivot table doenot update and I get a
print out of old data. How do I pivot table to update? Where and how do I
put the update function in my excel report?

Will this update also update the value() function? That function changes
text into a number that my pivot table uses.

"Tom Ogilvy" wrote:

> Where is the Updateit macro located. Is it in a general module
> (insert=>Module) in the VBE?
>
> with both subs in a general module, it worked fine for me. The code ran
> whenever I selected a different sheet.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Excel Rookie" <Excel (E-Mail Removed)> wrote in message
> news:28898F18-27F0-412E-A8D6-(E-Mail Removed)...
> > Hi
> > I got tired of keep create the new pivot table every time there're new
> > data
> > in the sheet. Therefore, I use the following code in the macro to update
> > but
> > it keeps giving an error '!UpdateIt' cannot be found ??? ~ PLS HELP !
> >
> > Sub Auto_Open()
> > Application.OnSheetActivate = "UpdateIt"
> > End Sub
> >
> > Sub UpdateIt()
> > Dim iP As Integer
> > Application.DisplayAlerts = False
> > For iP = 1 To ActiveSheet.PivotTables.Count
> > ActiveSheet.PivotTables(iP).RefreshTable
> > Next
> > Application.DisplayAlerts = True
> > End Sub
> >
> > Thanks,
> > Excel Rookie
>
>
>