Solved

Refreshing pivot table via VBA

Posted on 2011-09-26
16
198 Views
Last Modified: 2012-05-12
    Worksheets("Pivots").Activate
    ActiveSheet.PivotTables("MyPivots").RefreshTable

Open in new window


Hi I have the above code and the pivot below (you can see the pivot name and table name) but its giving me an error "Error 1004: The pivot table name is not valid"

Any idea why?

The name is correct!



Thanks! Image
0
Comment
Question by:Shanan212
  • 6
  • 4
  • 4
  • +1
16 Comments
 
LVL 12

Expert Comment

by:danishani
ID: 36601550
Check if its the right name, to make sure click on the PivotTable Options and see what Name appears in the first Tab, that should be the PivotTableName.

Hope this helps,
Daniel
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36646190
This appears to be an Excel issuse, yet you have posted it in the Access zone.

Please click the "request attention" link and ask that the Excel zone be added to this Q.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36646860
Please post the entire procedure
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36660991

1. Does this work if you try it on a new, simple pivot table?

2. It is not clear how you "Named" the PT?

In any event, try this:
This will refresh "any" PT on the sheet:

Dim pt As PivotTable
    Worksheets("Pivots").Activate
    For Each pt In ActiveSheet.PivotTables
        pt.RefreshTable
    Next pt

Also try to avoid sheet names like "Pivots" because this may cause conflicts with certain Excel Internal keywords.
Instead use more descriptive names like "SalesPivot", TaxPerVendorPT, ...etc

JeffCoachman

0
 
LVL 13

Author Comment

by:Shanan212
ID: 36661810
The procedure itself is a function. This is the first time I am touching this pivot table/worksheet that this pivot table is in.

The BIG function above this takes care of creating a merged data sheet.

But as you can see in the file itself, the name is same! Even in pivot table options, its the same. Same error even if I change the file name to a different one (and alike in code)

Thanks for the attempt!
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36664088
Jeff,

Still same error.

I recreated the pivot and type in the default name and same error.  :/

Seems like a simple error (I feel like I am overlooking something) But I can guarantee you its not the name :o

 screenshot
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36707519
In your screenshot, my loop code is commented out, so it is not clear what you actually tested here...

How about just posting this DB (or a sample that produced this error), this way we avoid all the guesswork?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36709379
Shanan212,

If you insist on not posting the code in its entirety, then no one will be able to help you.

Patrick
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36709546
I found out the error. Its because the source data is changed!
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36709569
I've requested that this question be closed as follows:

Accepted answer: 500 points for boag2000's comment http:/Q_27328534.html#36660991
Assisted answer: 0 points for Shanan212's comment http:/Q_27328534.html#36709546

for the following reason:

Thanks!
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36709570
With respect, if the source data changing was the true issue, then http:#a36660991 has nothing to do with the solution.

Please post a full description of how you solved it yourself, and then select that as the answer.  If you are unable or unwilling to do that, then the right way t close this is for you to delete the question.

Patrick
0
 
LVL 13

Accepted Solution

by:
Shanan212 earned 0 total points
ID: 36709891
Ok,

This is what I did:

My souce table is deleted every time a macro is run and recreated with new data (but same columns)

This has an effect on the source data of pivot table as the next time the macro is run, the source data is changed automatically to refer to an empty patch in source table.

What I have now is, insert a named range of the source data.

And re-affirmed the named range via this vba

Sheets("GL Summary").Range("A1").Currentregion.name = "Ranges"

Open in new window


This is preventing from name range referencing to '#REF' when the source table/worksheet is deleted (which being the original problem)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36710049
<My souce table is deleted every time a macro is run and recreated with new data>

...this very important detail was never mentioned...


So like Patrick, states, (since you clearly posted your own solution), you can accept your own post as the Solution.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36710594
Shanan212,

Thank you for taking the time to post your solution, so future readers can benefit.

Please go ahead and select your comment http:#a36709891 as the solution, and nice going on figuring it out.

Patrick
0
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 36898933
solved
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

863 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

21 Experts available now in Live!

Get 1:1 Help Now