Pivot Update Error - VBA

rconverse

Well-known Member
Joined
Nov 29, 2007
Messages
1,187
Hello,

I'm using a loop to update a pivot report field. At random times during the loop, I receive an error. The c1.value appears to be correct. The error I receive is Run Time Error 5: "Invalid procedure call or argument" on the pivot update line below. I'm confused because the error only occurs randomly.

Rich (BB code):
'set range of taxonomies from which to loop
Set r1 = ws1.Range("$A$5:$A$50")

'set range of taxonomies to loop
For Each c1 In r1
    
    'update pivot in "pivot-taxonomies" to next taxonomy
    ws2.PivotTables("PivotTable2").PivotFields("Taxonomy").CurrentPage = c1.Value
...

Next

Any help appreciated. Thanks!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
What is in the range A5:A50? Is this part of the pivot table? Do you really want to be looping over the values of a pivot field?

Would it not make more sense to do something like:
Code:
Dim pitem as PivotItem

For Each pitem in ws2.PivotTables("PivotTable2").PivotFields("Taxonomy").PivotItems
   ws2.PivotTables("PivotTable2").PivotFields("Taxonomy").CurrentPage = pitem.Name



Next
 
Upvote 0
Yes, that should work better but, unfortunately, I'm still getting the same weird error. For example, the routine went through that loop about 10 times before that error popped up.

Basically, I am trying to loop through two pivot tables and then do a bunch of action to a third worksheet.

The basic premise is that I have a list of 5,000+ records and 270+ taxonomies (or categories). Within each category is x items. What I need to do is, by taxonomy, find every item and then do a bucnch of stuff in the third worksheet. The good news is I have the "bunch of stuff in the third worksheet" working correctly. The first pitem becomes the report filter in the second pivot. Then, I iterate through those items and refresh with the next pitem.value.

New code:

Code:
'set variable for workbook and worksheet
Set wb1 = ThisWorkbook
Set ws1 = wb1.Sheets("PIVOT-TAXONOMY")
Set ws2 = wb1.Sheets("PIVOT-PRICES")
Set ws3 = wb1.Sheets("DETAIL")

'refresh all pivots in workbook
wb1.RefreshAll

'set range of taxonomies to loop
For Each pitem In ws1.PivotTables("pvtTaxonomy").PivotFields("Taxonomy").PivotItems

    'update pivot in "pivot-taxonomies" to next taxonomy
    ws2.PivotTables("pvtPrices").PivotFields("Taxonomy").CurrentPage = pitem
...
Next

I should add that, when this error occurs, I can manually update my second pivot table (pvtPrices) and then restart the code, but I have to do that for each time I want to update my second pivot (which is over 270 times).
 
Upvote 0
What is the error that is coming up? Is it possible that the item it is trying to set the page to no longer exists in the data?

Is it always the same page? is it the last value?
 
Upvote 0
Same error.

Run Time Error 5: "Invalid procedure call or argument"

I am not exactly sure how but I was able to get it to work. Theoretically no, that cannot happen with this setup. (Better yet, that should not happen based on its intended setup.) For example, when the first pivot table refreshes (pivotrange has a dynamic range based on named formula of the deatil), any new taxonomies or ones that no longer exist, should go away with the refreshing of that pivot.

So, if my first taxonomy in my first pivot was "Abrasives" and I remove all the abrasive records from my detail (from where the pivot is derived), when that list of taxonomies refreshses, "Abrasives" should be gone and "Ball Bearings" should be the first taxonomy. Therefore, the routine would take "Ball Bearings" and update the second pivot as that report header. That's why your error shouldn't occur.

The only thing of which I can think is that I changed the filter to all before starting my loop. Before I would have it on some value. Would I potentially receive that error if I was trying to update that pivot field with that value that was already populated? For example, I have my filter set to "Abrasives" and then I try to update again with "Abrasives".

Thanks for your time and help, btw. I definitely appreciate it.
 
Upvote 0
It should not be a problem to update to a value that it is already set to.

There is a dropdown on the pivot table options under the data tab that says "Number of Items to retain per field" Try setting that to NONE. Unfortunately, refreshing the data does not clear any values that no longer have data.

You can see what the value for the pitem.name is when you get the error, then manually check the page dropdown on the pivot and see if that is still in there. Setting the option to NONE should make that problem go away.
 
Upvote 0
I have made that change, thank you. As mentioned, I was able to get this to run, so hopefully I'l be set moving forward. Thanks again!!
 
Upvote 0

Forum statistics

Threads
1,203,456
Messages
6,055,543
Members
444,794
Latest member
HSAL

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top