Using VBA to Modify Pivot Table

bisel

Board Regular
Joined
Jan 4, 2010
Messages
152
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

bisel

Board Regular
Joined
Jan 4, 2010
Messages
152
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.
 

bisel

Board Regular
Joined
Jan 4, 2010
Messages
152
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,582
Messages
5,548,878
Members
410,881
Latest member
toonces
Top