I have a data table which is summarised using a pivot table. Some fields are excluded using the report filter manually and I want to do this in code after the user has entered a date range into 2 textboxes.
In order to get a grasp of this I ran a macro which gave me the following code
Sub Macro1()
'
' Macro1 Macro
'
'
ActiveSheet.PivotTables("PivotTable1").PivotFields("INV DATE").CurrentPage = _
"(All)"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("INV DATE")
.PivotItems("(blank)").Visible = False
.PivotItems("07/12/2017").Visible = False ***************
.PivotItems("08/12/2017").Visible = False
.PivotItems("11/12/2017").Visible = False
End With
End Sub
When I try and run this macro I get an error report "Unable to get the PivotItems property of the PivotField Class" on the line marked *****
When I check, all the pivotItems are in the list produced by the filter which has reset to "All"
Where am I going wrong and how can I generalise the code.
The attached workbook shows the pivottable. The data is not shown.
John
Bookmarks