Use VBA to change Pivot Table Filter

[Healey33]

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 :

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

 

[pronto8285]

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 :

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

Try Adding This instead of the current items:
.PivotItems("All").Visible = True
.PivotItems("Blank").Visible = False

.PivotFields("LEASE SIGNED BY LANDLORD").PivotItems("(blank)").Visible = False

 

[Healey33]

Try Adding This instead of the current items:
.PivotItems("All").Visible = True
.PivotItems("Blank").Visible = False

.PivotFields("LEASE SIGNED BY LANDLORD").PivotItems("(blank)").Visible = False

Ok so, I tried that and it DOES uncheck "(blank)" (This is what I wanted) but when I add the "(All)" line, that is where I receive the error. I don't understand why you wouldn't be allowed to check the "All" checkbox using code. Any ideas?

I have the code for the "All" line the same as the blank one but I will post it anyways.

With Sheets("Sales Dashboard input").PivotTables("PivotTable1")
.PivotFields("LEASE SIGNED BY LANDLORD").PivotItems("(All)").Visible = True
.PivotFields("LEASE SIGNED BY LANDLORD").PivotItems("(blank)").Visible = False
End With

 

[pronto8285]

Well All should be selected by default right? unless upon the time of macro run, the filter is in place and all needs to be checked.

 

[Healey33]

Well All should be selected by default right? unless upon the time of macro run, the filter is in place and all needs to be checked.

Silly me. Yes, I've now checked "all" and the code just unchecks blank. Thank you, this works exactly as desired.

 

[pronto8285]

Silly me. Yes, I've now checked "all" and the code just unchecks blank. Thank you, this works exactly as desired.

Awesome. Glad i could help.

 

[Healey33]

I am still having trouble with enabling a filter (setting a pivot items property to true). I have another pivot table that shows just the past week, filtering on a date field and i have it looping through, checking only the records form the past week. Here is the code:

Dim pt As PivotTable
Dim x As Integer
Dim day As Date
Dim day1 As Date
day = Now()
x = 0
    Worksheets("Sales Dashboard input").Select
    For Each pt In ActiveSheet.PivotTables
        pt.RefreshTable
    Next pt
    
    Worksheets("Eng Items").Select
    For Each pt In ActiveSheet.PivotTables
        pt.RefreshTable
    Next pt
    Worksheets("Sales Dashboard input").Select
With Sheets("Sales Dashboard input")
.PivotTables("PivotTable22").ManualUpdate = True
Application.EnableEvents = False
Application.ScreenUpdating = False
.PivotTables("PivotTable1").PivotFields("LEASE SIGNED BY LANDLORD").PivotItems("(blank)").Visible = False
Do While x < 7
day1 = day - x
.PivotTables("PivotTable22").PivotFields("LEASE SIGNED BY LANDLORD").PivotItems(day1).Visible = True
Loop


End With

I receive the same error, "Run-time error '1004': Unable to get the PivotItems property of the PivotFields class" on the ".PivotTables("PivotTable22").PivotFields("LEASE SIGNED BY LANDLORD").PivotItems(day1).Visible = True" line

Is there something I need to do differently in order to set the pivotitems property to True?