retty much a VBA novice, I am trying to update date filter on 3 Pivot tables that have been grouped by Date to Month/year, I want to make it dynamic so that start and end date can be updated
I have written the code below but get an invalid date error when try to run it
if I change line with Type:=xlDateBetween, Value1:=Range("StartDate"), Value2:=Range("EndDate")
to
Type:=xlDateBetween, Value1:="01/09/2012", Value2:="31/08/2013"
it works fine. The cells that I reference are in the same format as above.
Any help would be appreciated
Dim PtC As PivotTable
Dim PtF1 As PivotField
Dim PtF2 As PivotField
Dim PtF3 As PivotField
Worksheets("Availability").Select
Set Pt = ActiveSheet.PivotTables("Financial")
Set PtF1 = Pt.PivotFields("Raised")
Set PtB = ActiveSheet.PivotTables("IncidentCount")
Set PtF2 = PtB.PivotFields("Raised")
Set PtC = ActiveSheet.PivotTables("RAGCount")
Set PtF3 = PtB.PivotFields("Raised")
Set StartDate = Worksheets("Workings").Range("F2")
Set EndDate = Worksheets("Workings").Range("G2")
Pt.ClearAllFilters
PtF1.PivotFilters.Add _
Type:=xlDateBetween, Value1:=Range("StartDate"), Value2:=Range("EndDate")
PtB.ClearAllFilters
PtF2.PivotFilters.Add _
Type:=xlDateBetween, Value1:=Range("StartDate"), Value2:=Range("EndDate")
PtC.ClearAllFilters
PtF3.PivotFilters.Add _
Type:=xlDateBetween, Value1:=Range("StartDate"), Value2:=Range("EndDate")
End Sub
Bookmarks