Solved

Refresh PivotTables using macro

Posted on 2001-07-04
11
805 Views
Last Modified: 2012-08-13
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
Comment
Question by:Ecmil
  • 3
  • 3
  • 2
  • +3
11 Comments
 
LVL 13

Expert Comment

by:cri
ID: 6252092
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
 

Author Comment

by:Ecmil
ID: 6252200
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
 
LVL 13

Expert Comment

by:cri
ID: 6252662
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
 

Expert Comment

by:auzan
ID: 6252767
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
 

Author Comment

by:Ecmil
ID: 6252820
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 13

Expert Comment

by:cri
ID: 6253184
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
 
LVL 7

Expert Comment

by:q2eddie
ID: 6256174
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
 
LVL 16

Expert Comment

by:sebastienm
ID: 6256823
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
 

Author Comment

by:Ecmil
ID: 6269921
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
 
LVL 7

Accepted Solution

by:
q2eddie earned 0 total points
ID: 6269946
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
 
LVL 2

Expert Comment

by:Lunchy
ID: 6271884
Points refunded, and question added to PAQ.

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

0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Article by: dandraka
There are several quick shortcuts that can make your life easier in Microsoft Programs.  These simple tips and tricks will your work more productive and you faster at completing your tasks! MS Word (1) Creating Re-usable Scraps You can create s…
Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now