• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

Refreshing pivot table via VBA

    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
Shanan212
Asked:
Shanan212
  • 6
  • 4
  • 4
  • +1
1 Solution
 
danishaniCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
Patrick MatthewsCommented:
Please post the entire procedure
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jeffrey CoachmanMIS LiasonCommented:

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
 
Shanan212Author Commented:
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
 
Shanan212Author Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
Patrick MatthewsCommented:
Shanan212,

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

Patrick
0
 
Shanan212Author Commented:
I found out the error. Its because the source data is changed!
0
 
Shanan212Author Commented:
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
 
Patrick MatthewsCommented:
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
 
Shanan212Author Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
<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
 
Patrick MatthewsCommented:
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
 
Shanan212Author Commented:
solved
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 6
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now