Run-time error '1004': Unable to get the PivotTables property of the Worksheet class

[ratedr95]

Hi,

I am trying to get a macro to run to automatically change the filters in a Pivot table linked to a Cube.

I recorded the macro to begin with but I always get the error listed in the title of this thread.

My code is:

Sub CubeReconcile()
    
    Application.ScreenUpdating = False
    Workbooks.Open ("P:\Desktop\Cube v1.xlsm")
    ThisWorkbook.Activate
    Application.ScreenUpdating = True
    
    ActiveSheet.PivotTables("ECBv1.0").PivotFields( _
        "[Business_Unit].[Business Unit Hierarchy].[Business_Unit]"). _
        ClearAllFilters

    ActiveSheet.PivotTables("Cubev1.0").PivotFields( _
        "[Business_Unit].[Business Unit Hierarchy].[Business_Unit]"). _
        CurrentPageName = _
        "[Business_Unit].[Business Unit Hierarchy].[Business_Unit].&[European]"
    
End Sub

The line it is pointing to is the final line in the code beginning with "[Business_Unit]..

Any ideas that could be the reason for the error?

 

[ratedr95]

    ActiveSheet.PivotTables("Cubev1.0").PivotFields( _
        "[Business_Unit].[Business Unit Hierarchy].[Business_Unit]"). _
        ClearAllFilters

This does say "CubeV1.0" by the way, not "ECBv1.0"

 

[RoryA]

That error indicates that either the pivot table name is wrong, or you have the wrong sheet active.

 

[ratedr95]

I removed all sheets from the file (Aside the one with the table on), re-recorded the Macro and then tried to run the Macro again and it is still giving the same error highlighting the same line of code.

Any other suggestions?