Auto Update Pivot Table Help

E

Excel Rookie

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
 
T

Tom Ogilvy

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.
 
J

Jayneedshelp

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top