Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Update Pivot Table Help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Update Pivot Table Help
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Update Pivot Table Help
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 wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Update Pivot Table Help
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 wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Update Pivot Table Help
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto update my pivot table | Excel Discussion (Misc queries) | |||
Auto Update a Pivot Table | Excel Discussion (Misc queries) | |||
Auto update of Pivot Table | Excel Discussion (Misc queries) | |||
Auto Update Range of a Pivot Table | Excel Programming | |||
How do I set the pivot table to auto-update the data range? | Excel Discussion (Misc queries) |