Nutterhead
New Member
- Joined
- Apr 25, 2017
- Messages
- 27
I need some Newbie help. I have a code I put together from other post to filter a pivot table based of a cell's data. Now it works fine if I am filtering my "department" filter but I need to filer based off a date. I can't get this to work and it keeps showing an error on Field.CurrentPage = New Cat. I'm assuming this is because the date should not be a string or something like that? Please help?!
Sub Pivot_Update()
'Set the Variables to be used
Dim pt As PivotTable
Dim pt2 As PivotTable
Dim Field As PivotField
Dim NewCat As String
'Update & Refresh Pivot 1 on the sheet
Set pt = Worksheets("1st Time").PivotTables("PivotTable1_1")
Set Field = pt.PivotFields("Date")
NewCat = Worksheets("1st Time").Range("H2").Value
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
'Update & Refresh Pivot 2 on the sheet
Set pt2 = Worksheets("1st Time").PivotTables("PivotTable1_2")
Set Field = pt2.PivotFields("Date")
NewCat = Worksheets("1st Time").Range("H2").Value
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
End Sub
Sub Pivot_Update()
'Set the Variables to be used
Dim pt As PivotTable
Dim pt2 As PivotTable
Dim Field As PivotField
Dim NewCat As String
'Update & Refresh Pivot 1 on the sheet
Set pt = Worksheets("1st Time").PivotTables("PivotTable1_1")
Set Field = pt.PivotFields("Date")
NewCat = Worksheets("1st Time").Range("H2").Value
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
'Update & Refresh Pivot 2 on the sheet
Set pt2 = Worksheets("1st Time").PivotTables("PivotTable1_2")
Set Field = pt2.PivotFields("Date")
NewCat = Worksheets("1st Time").Range("H2").Value
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
End Sub