says run time error 1004 unable to set the orientation property of the pivotfield class

user_16553834 0
Viewed 391

Having issues on the below code:

ActiveSheet.PivotTables("PivotTable1").PivotFields("WeekNumIss").Orientation = _
    xlHidden

says run time error 1004 unable to set the orientation property of the pivotfield class

please help how to remove it. Thanks! :(

Sub fixblanks()

Application.ScreenUpdating = False
ActiveWorkbook.Sheets("Dash").Activate

    ActiveSheet.PivotTables("PivotTable1").PivotFields("IssueMonth"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable2").PivotFields("IssueMonth"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable3").PivotFields("IssueMonth"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable4").PivotFields("IssueMonth"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable5").PivotFields("IssueMonth"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable6").PivotFields("IssueMonth"). _
        ClearAllFilters
        
    ActiveSheet.PivotTables("PivotTable1").PivotFields("WeekNumIss").Orientation = _
        xlHidden
        
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("IssueMonth")
        .PivotItems("(blank)").Visible = False
    End With
    On Error Resume Next
        ActiveSheet.PivotTables("PivotTable2").PivotFields("WeekNumIss").Orientation = _
        xlHidden
        
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("IssueMonth")
        .PivotItems("(blank)").Visible = False
    End With
    
    ActiveSheet.PivotTables("PivotTable3").PivotFields("WeekNumIss").Orientation = _
        xlHidden
        
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("IssueMonth")
        .PivotItems("(blank)").Visible = False
    End With
    
    ActiveSheet.PivotTables("PivotTable4").PivotFields("WeekNumIss").Orientation = _
        xlHidden
        
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("IssueMonth")
        .PivotItems("(blank)").Visible = False
    End With
    
        ActiveSheet.PivotTables("PivotTable5").PivotFields("WeekNumIss").Orientation = _
        xlHidden
        
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("IssueMonth")
        .PivotItems("(blank)").Visible = False
    End With
    
            ActiveSheet.PivotTables("PivotTable6").PivotFields("WeekNumIss").Orientation = _
        xlHidden
        
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("IssueMonth")
        .PivotItems("(blank)").Visible = False
    End With
        
    
End Sub
vba excel
1 Answers

The error you are getting is saying that Excel can't find WeekNumIss in PivotTable1.
There are a few reasons this may be:

  1. I see you are using ActiveSheet in your code. If PivotTable1 isn't in the active sheet then you will get this error. Instead, you could use Sheets([SheetName])

  2. WeekNumIss isn't included in the Rows, Columns, or Filter section of PivotTable1.

  3. The name WeekNumIss or PivotTable1 doesn't match what is in the workbook exactly.

I hope this helps! If not, please let me know so I can debug a bit more.
A picture of the pivot table in question would be helpful as well.

user_71811860
Related