Date Range Filter on a Pivot Table?

[Christof]

Can someone check to see if my coding is correct on this as I can't seem to get it to work.


Basically I want to be able to update values in cells H2, H4, H6 and H7 that will drive the pivot table.
So H6 and H7 wants to be the date range that will drive the selections for "Confirmed Date" on the filter part of the Pivot.

Any ideas where I'm going wrong?

Below is my script and my example file is also attached.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'This line stops the worksheet updating on every change, it only updates when cell
'G10 or G11 is touched
If Intersect(Target, Range("G10:G11")) Is Nothing Then Exit Sub

'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String

Dim pt2 As PivotTable
Dim Field2 As PivotField
Dim NewCat2 As String

Dim pt3 As PivotField
Dim pt4 As PivotItem



'Here you amend to suit your data
Set pt = Worksheets("Top (Cons)").PivotTables("PivotTable1")
Set Field = pt.PivotFields("Brand")
NewCat = Worksheets("Top (Cons)").Range("h2").Value


Set pt2 = Worksheets("Top (Cons)").PivotTables("PivotTable1")
Set Field2 = pt2.PivotFields("Consortium")
NewCat2 = Worksheets("Top (Cons)").Range("h4").Value


Set pt3 = Worksheets("Top (Cons)").PivotTables("PivotTable1").PivotFields("Confirmed Date")
For Each pt4 In pt3.PivotItems
    If DateValue(pt4.Name) >= Range("h7").Value2 And DateValue(pt4.Name) <= Range("h6").Value2 Then
        pt4.Visible = True
    Else
        pt4.Visible = False
    End If
Next pt4


       
'This updates and refreshes the PIVOT table

With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

With pt2
Field2.ClearAllFilters
Field2.CurrentPage = NewCat2
pt2.RefreshTable
End With

End Sub

 

[Nebu]

Hi:
Use the following code

Sub test()

'Set the Variables to be used
Dim pt As PivotTable
Dim Field, Field2, Field3 As PivotField
Dim NewCat, NewCat2 As String
Dim pi As PivotItem

'Here you amend to suit your data
Set pt = Worksheets("Top (Cons)").PivotTables("PivotTable1")
Set Field = pt.PivotFields("Brand")
Set Field2 = pt.PivotFields("Consortium")
Set Field3 = pt.PivotFields("Confirmed_Date")

NewCat = Worksheets("Top (Cons)").Range("h2").Value
NewCat2 = Worksheets("Top (Cons)").Range("h4").Value

With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
Field2.ClearAllFilters
Field2.CurrentPage = NewCat2
Field3.ClearAllFilters

'For Each pi In Field3.PivotItems
'If CDate(pi.Value) <= CDate(Sheet1.[H7].Value) And CDate(pi.Value) >= CDate(Sheet1.[H6].Value) Then
'On Error Resume Next
'pi.Visible = True
'Else
'pi.Visible = False
'End If
'Next
pt.RefreshTable

End With

End Sub

I tried the filter for your date as well, but your date format is not in the correct format hence the filtering will be difficult.

Thanks

 

[Christof]

Thanks.
Is it possible to change the formatting of my dates so that it would work?
What would I need to do?