Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 840
  • Last Modified:

Refresh PivotTables using macro

Hi!

I need someone who can explain this:

I have a PivotTable in excel and I have recorded a macro that refreshes the PivotTable. Looks like this:
ActiveSheet.PivotTables("PivotTable1").PivotSelect "Data[All]", xlLabelOnly
ActiveSheet.PivotTables("PivotTable1").RefreshTable

This macro only works if I execute it from the tools menu/macro/play mecro, if a execute it from a CommandButton I only receive Runtime error ?1004?.

Can someone please help me with this? Maybe there are different ways of refreshing pivottables that I don?t know.

/Ecmil
0
Ecmil
Asked:
Ecmil
  • 3
  • 3
  • 2
  • +3
1 Solution
 
criCommented:
Where is the table of the pivot table ?
See http://support.microsoft.com/support/kb/articles/Q157/3/83.asp


If this does not solve it, a shot in the dark: Put

MsgBox ActiveSheet.Name

before the lines above to find out if the focus is lost when activated from the CommandButton
0
 
EcmilAuthor Commented:
I looked at what microsoft had to say but it didn't help because my PivotTable doesn't have the same range all the time.

I also checked the Activesheet.Name and it was correct.

As I mentioned before, I can refresh PT if I use the Tools menu och run the macro from there but not from a commandbutton or any object at all.
0
 
criCommented:
Use SendKeys (gasp!)...

Sub PossibleButNotRecommended()
  MsgBox "I warned you"
End Sub

Sub test()
  Application.SendKeys "%TMPossibleButNotRecommended{ENTER}"
End Sub

Leave this question open, perhaps a sensible solution will come up
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
auzanCommented:
Different ways of refreshing pivottables.
 You have a refresh data button in the PivotTable tool bar
(The button symbol is like !). you can use this button also with the right clik mouse button.
0
 
EcmilAuthor Commented:
Auzan! I'm aware of the ! for refreshing PT, I recorded my macro that way. The only problem is that the macro doesn't work during runtime.

cri - what?? can you explain your code?
0
 
criCommented:
Assuming your Sub is called 'RefreshPivotTable'

then put this go-between macro in a module of _workbook_ (*)

Sub CallMyRefreshPivotTableMacro()
 Application.SendKeys "%TMRefreshPivotTable{ENTER}"
End Sub

and link it with to your CommandButton.

(*) it _should_ even work if you put this into an add-in, even if does not appear in the macro dialog box.





0
 
q2eddieCommented:
Hi, Ecmil.

#Try This
' Refresh the pivotTable's datacache.
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

#Links
1. "PivotCache Object"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/xlobjPivotCache.asp

2. "PivotCaches Collection Object"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/xlobjPivotCaches.asp

Bye. -e2
0
 
sebastienmCommented:
Are you sure the name of your Pivot is still PivotTable1 (which would have been changed if you have re-done your pivot, or something similar)

Please, try:
ActiveSheet.PivotTables(1).RefreshTable

Regards,
Sebastien
0
 
EcmilAuthor Commented:
Sorry!
None of your suggestion solved my problem. Thanks for trying. Anyhow, I managed to solve it myself by adding
Range("A1").Select in the subrutine?
 
Sub RefreshPT()
Range("A1").Select

    Application.DisplayAlerts = False

        ActiveSheet.PivotTables("PivotTable1").PivotSelect "Data[All]", xlLabelOnly
        ActiveSheet.PivotTables("PivotTable1").RefreshTable

    Application.DisplayAlerts = True

Range("A1").Select

End Sub

?and this works just fine. I'm thinking of delete this question unless on of you guys who commented the question as any objections. Maybe you think you solved it or someone else involved in the question.

I want to hear your opinion on this!
0
 
q2eddieCommented:
Hey, Ecmil.

Instead of deleting this question, you could ask Community Support to refund your question points.

Post a zero-point question in the Community Support area with a title like "Answered my own Q" asking for:
a. refund of your question points
b. closing the question

Bye. -e2
0
 
LunchyCommented:
Points refunded, and question added to PAQ.

Lunchy
Friendly Neighbourhood Community Support Moderator
Lunchy@experts-exchange.com

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now