?
Solved

Refresh PivotTables using macro

Posted on 2001-07-04
11
Medium Priority
?
823 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

777 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