Hey, I have a Pivot table called "PivotTable1" in Excel and would like to change the filter on it using VBA. The field I would like to change is a date field called "LEASE SIGNED BY LANDLORD". I would like to select all of the dates and leave out the blanks (doing this manually requires checking the "All" checkbox then unchecking the "Blanks" so I am assuming the code would have to be in that order as well?) The code I have so far is :
I receive the error "Run-time error '1004': Unable to get the PivotItems property of the PivotFields class" on the .PivotItems("All").Visible = True line. I have triple checked that the sheet name, pivot table name and field name are correct so that is not the issue.
This is slightly urgent (I need it working for tomorrow night) so any help is greatly appreciated!
Thanks,
Healey33
Code:
With Sheets("Sales Dashboard input").PivotTables("PivotTable1").PivotFields("LEASE SIGNED BY LANDLORD")
.PivotItems("All").Visible = True
.PivotItems("Blank").Visible = False
End With
I receive the error "Run-time error '1004': Unable to get the PivotItems property of the PivotFields class" on the .PivotItems("All").Visible = True line. I have triple checked that the sheet name, pivot table name and field name are correct so that is not the issue.
This is slightly urgent (I need it working for tomorrow night) so any help is greatly appreciated!
Thanks,
Healey33