Solved

Refresh PivotTables using macro

Posted on 2001-07-04
11
814 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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
 
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 Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
Outlook Free & Paid Tools
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:
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

808 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