Hi guys,

I have been trying to find a way to autofiltering date range using vba code and luckly have found the following vba code which does exactly what i want to achive and it works fine.
Now, the problem that I have is that I have more than 1 pivot table in the worksheet and I would like the code to apply to all the pivot tables in the worksheet at the same time.

Can someone please amend the code below to achive this?

Much appreciated.

Kind regards, Jim

Private Sub Worksheet_Change(ByVal Target As Range)

***

****If Target.Cells.Count > 1 Then Exit Sub

***

****Dim ptPvt** As PivotTable

*** Dim ptFld** As PivotField

*** Dim i****** As Long

*** Dim Flg**** As Boolean

*** Dim dtBegin As Date

*** Dim dtEnd** As Date

*

*** Set ptPvt = Me.PivotTables(1)

*** Set ptFld = ptPvt.PivotFields("Order Status Date") '<<==== adjust the field

*

*** With Application

******* .EnableEvents = False

******* .ScreenUpdating = False

*** End With

***

****ptFld.ClearAllFilters

*** ptFld.EnableMultiplePageItems = True

*** ptFld.CurrentPage = "(All)"

***

****Select Case Target.Address(0, 0)

******* Case "C1"

*********** dtBegin = CDate(Target)

*********** 'assure it's the first day

*********** dtBegin = DateSerial(Year(dtBegin), Month(dtBegin), 1)

*********** dtEnd = CDate(Me.Range("C2"))

*********** 'last day

*********** dtEnd = DateSerial(Year(dtEnd), Month(dtEnd) + 1, 1) - 1

*********** Flg = CDate(Me.Range("C2"))

*********** If Flg And dtBegin > dtEnd Then

*************** MsgBox "Date End must be greater than Date begin", vbInformation

*************** Application.EnableEvents = True

*************** Exit Sub

*********** End If

*********** With ptFld

*************** If Flg Then

*** ****************For i = 1 To .PivotItems.Count

*********************** If .PivotItems(i) < dtBegin Or .PivotItems(i) > dtEnd Then

*************************** .PivotItems(i).Visible = False

*********************** End If

******************* Next

********** *****Else

******************* For i = 1 To .PivotItems.Count

*********************** If .PivotItems(i) < dtBegin Then

*************************** .PivotItems(i).Visible = False

*********************** End If

******************* Next

*************** End If

*********** End With

******* Case "C2"

*********** dtEnd = CDate(Target)

*********** dtEnd = DateSerial(Year(dtEnd), Month(dtEnd) + 1, 1) - 1

*********** Flg = CDate(Me.Range("C1"))

*********** dtBegin = CDate(Me.Range("C1"))

*********** dtBegin = DateSerial(Year(dtBegin), Month(dtBegin), 1)

*********** If Flg And dtEnd < dtBegin Then

*************** MsgBox "Date End must be greater than Date begin", vbInformation

*************** Application.EnableEvents = True

*************** Exit Sub

*********** End If

* **********With ptFld

*************** If Flg Then

******************* For i = 1 To .PivotItems.Count

*********************** If .PivotItems(i) < dtBegin Or .PivotItems(i) > dtEnd Then

*************************** .PivotItems(i).Visible = False

************* **********End If

******************* Next

*************** Else

******************* For i = 1 To .PivotItems.Count

*********************** If .PivotItems(i) > dtEnd Then

*************************** .PivotItems(i).Visible = False

*********************** End If

******************* Next

*************** End If

*********** End With

*** End Select

*

*** With Application

******* .EnableEvents = True

******* .ScreenUpdating = True

*** End With

***

End Sub