Solved

Refreshing pivot table via VBA

Posted on 2011-09-26
16
195 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
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 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

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