I am using a macro to update the a filter on a pivot table. The below script works great when the field is text however I can not get it to work with a date value.
I have tried several differnt changes but cannot get it to work. It will clear the filter but does not appear to recognise the date value from the cell as a valid selection. I have even tried to converting the date to a text value first with no luck.
My Pivot table (Campaigns) has two filters (Group and Date) which both need to be updated from different cells (A1 and B1). Below is the script that works and updates the Group filter.
Any help would be much appreciated. I am new to playing with VBA and seem to be completely lost with this one.
Sub Apply_Reporting_Group_Filter()
Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem
Dim filterReportingGroup As String
Set pvtTable = Worksheets("Sheet1").PivotTables("Campaigns")
Set pvtField = pvtTable.PivotFields("Reporting Group")
filterReportingGroup = Worksheets("Data Control").Range("A1")
For Each pvtItem In pvtField.PivotItems
If pvtItem.Value = filterReportingGroup Then
pvtField.CurrentPage = filterReportingGroup
Exit For
End If
Next pvtItem
End Sub
I have tried several differnt changes but cannot get it to work. It will clear the filter but does not appear to recognise the date value from the cell as a valid selection. I have even tried to converting the date to a text value first with no luck.
My Pivot table (Campaigns) has two filters (Group and Date) which both need to be updated from different cells (A1 and B1). Below is the script that works and updates the Group filter.
Any help would be much appreciated. I am new to playing with VBA and seem to be completely lost with this one.
Sub Apply_Reporting_Group_Filter()
Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem
Dim filterReportingGroup As String
Set pvtTable = Worksheets("Sheet1").PivotTables("Campaigns")
Set pvtField = pvtTable.PivotFields("Reporting Group")
filterReportingGroup = Worksheets("Data Control").Range("A1")
For Each pvtItem In pvtField.PivotItems
If pvtItem.Value = filterReportingGroup Then
pvtField.CurrentPage = filterReportingGroup
Exit For
End If
Next pvtItem
End Sub
Last edited:
