I need to add a second filter to my pivot table. Right now, I have the "PI" filter and it works great. I'd like to add a second filter for the pivotfield "DOB" to be equal to the date in cell Z1. Any ideas?
VBA Code:
'Overtime Check
Sheets("Combined Time").Select
Dim rng1 As Range
Dim sht1 As Worksheet
Dim pTable1 As PivotTable
Set rng1 = ActiveSheet.Cells(1, 1).CurrentRegion
Set sht1 = ActiveWorkbook.Worksheets.Add
sht1.Name = "Overtime Pivot"
Set pTable1 = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
rng1.Address, Version:=8).CreatePivotTable(TableDestination:= _
sht1.Cells(1, 1), TableName:="PivotTable" & Format(Time, "hhmmss"))
With pTable1
With .PivotFields("DOB")
.Orientation = xlPageField
End With
With .PivotFields("PR Code")
.Orientation = xlPageField
Dim PI As PivotItem
.EnableMultiplePageItems = True
For Each PI In .PivotItems
If PI = "9GK" Or PI = "9GL" Or PI = "9GM" Or PI = "9GP" Or PI = "9GQ" Or PI = "9GS" Or PI = "9J8" Or PI = "9J9" Or PI = "9JA" Or PI = "9JB" Or PI = "9JD" Or PI = "9JD" Or PI = "9JE" Or PI = "9JF" Or PI = "9JH" Or PI = "FLS" Or PI = "LE6" Or PI = "M2A" Or PI = "P40" Or PI = "V39" Then
PI.Visible = True
Else
PI.Visible = False
End If
Next PI
End With
With .PivotFields("SSN")
.Orientation = xlRowField
.Subtotals(1) = False
End With
.PivotFields("Reg Hours").Orientation = xlDataField
End With