Using VBA to Modify Pivot Table

bisel

Board Regular
Joined
Jan 4, 2010
Messages
223
Office Version
  1. 365
Platform
  1. Windows
Greetings All,

I have two Pivot Tables on a worksheet ... PivotTable2 and PivotTable3.

I am using VBA as part of the Worksheet_Activate event.

VBA Code:
...

    Sheet4.PivotTables("PivotTable3").PivotCache.Refresh
        With Sheet4.PivotTables("PivotTable3").PivotFields("Years Total")
            .ClearAllFilters
            .PivotItems("0").Visible = False
        End With

        With Sheet4.PivotTables("PivotTable3").PivotFields("Component")
            .ClearAllFilters
            .PivotItems("0").Visible = False
        End With
              
    Sheet4.PivotTables("PivotTable2").PivotCache.Refresh
        With Sheet4.PivotTables("PivotTable2").PivotFields("Years Total")
            .ClearAllFilters
            .PivotItems("0").Visible = False
        End With

        With Sheet4.PivotTables("PivotTable2").PivotFields("Category")
            .ClearAllFilters
            .PivotItems("0").Visible = False
        End With

...

Everything was working fine until today. Now, I get an error that cannot get the PivotItems property class.

error1004.jpg


Anyone know of what is happening? How to correct this?

Thanks,

Steve
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Hello again ...

Very weird things going on here.

So, I replace the code above with this code ...

VBA Code:
...
    Sheet4.PivotTables("PivotTable2").PivotCache.Refresh
    Sheet4.PivotTables("PivotTable3").PivotCache.Refresh

    Sheet4.PivotTables("PivotTable2").PivotFields("Years Total").ClearAllFilters
    Sheet4.PivotTables("PivotTable2").PivotFields("Years Total").EnableMultiplePageItems = True
    Sheet4.PivotTables("PivotTable2").PivotFields("Years Total").CurrentPage = "(All)"
    Sheet4.PivotTables("PivotTable2").PivotFields("Years Total").PivotItems("$0").Visible = False
   
    Sheet4.PivotTables("PivotTable3").PivotFields("Years Total").ClearAllFilters
    Sheet4.PivotTables("PivotTable3").PivotFields("Years Total").EnableMultiplePageItems = True
    Sheet4.PivotTables("PivotTable3").PivotFields("Years Total").CurrentPage = "(All)"
    Sheet4.PivotTables("PivotTable3").PivotFields("Years Total").PivotItems("$ 0").Visible = False
...

And this works fine. The key difference between the two Pivot Tables is the Pivot Items. Notice that in PivotTable3, the PivotItems("$ 0") whereas in PivotTable2, PivotItems("$0"). No space after the $ sign. Very strange as both tables use the same data source and all the cells on Sheet4 are formatted Currency, 0 decimals, $ sign. If I change PivotTable2 to "$ 0" or PivotTable3 to "$0", I will then error 1004. I will also get error if I omit the $ sign.

This is quite strange.
 
Upvote 0
This is not a very elegent solution, but I have now inserted a "On Error Resume Next" statement prior to the above code and an "On Error GoTo 0" statement after. I then added three different formats for each pivot table to try to catch any condition. Like this ...

VBA Code:
...
On Error Resume Next

    Sheet4.PivotTables("PivotTable2").PivotCache.Refresh
    Sheet4.PivotTables("PivotTable3").PivotCache.Refresh

    Sheet4.PivotTables("PivotTable2").PivotFields("Years Total").ClearAllFilters
    Sheet4.PivotTables("PivotTable2").PivotFields("Years Total").EnableMultiplePageItems = True
   
' Use all possible formats to ensure catching the correct one
    Sheet4.PivotTables("PivotTable2").PivotFields("Years Total").PivotItems("$ 0").Visible = False
    Sheet4.PivotTables("PivotTable2").PivotFields("Years Total").PivotItems("$0").Visible = False
    Sheet4.PivotTables("PivotTable2").PivotFields("Years Total").PivotItems("0").Visible = False
   
    Sheet4.PivotTables("PivotTable3").PivotFields("Years Total").ClearAllFilters
    Sheet4.PivotTables("PivotTable3").PivotFields("Years Total").EnableMultiplePageItems = True
   
' Use all possible formats to ensure catching the correct one
    Sheet4.PivotTables("PivotTable3").PivotFields("Years Total").PivotItems("$ 0").Visible = False
    Sheet4.PivotTables("PivotTable3").PivotFields("Years Total").PivotItems("$0").Visible = False
    Sheet4.PivotTables("PivotTable3").PivotFields("Years Total").PivotItems("0").Visible = False

On Error GoTo 0
...

This is working for now ...

Steve
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

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