Hi
I have a data set that contains training data showing who has done a certain course and who hasn't. For those people who haven't completed the course a 'days overdue' value is also shown. I need to create a pivot table to summarise just those who haven't completed the course and who are more than 30 days overdue. I know how to set the report filter to just show non completions but I can't work out how to set the days overdue filter to greater than 30. I'm don't think it's possible to set a report filter to anything other than exact values (?) however I have seen people using a work around to achieve the same result. I don't understand the work artound code at all though so unable to adapt it to use myself. The code i have so far to create my pivot table is below. Would be really grateful for any help anyone can provide.
Thanks in advance![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
I have a data set that contains training data showing who has done a certain course and who hasn't. For those people who haven't completed the course a 'days overdue' value is also shown. I need to create a pivot table to summarise just those who haven't completed the course and who are more than 30 days overdue. I know how to set the report filter to just show non completions but I can't work out how to set the days overdue filter to greater than 30. I'm don't think it's possible to set a report filter to anything other than exact values (?) however I have seen people using a work around to achieve the same result. I don't understand the work artound code at all though so unable to adapt it to use myself. The code i have so far to create my pivot table is below. Would be really grateful for any help anyone can provide.
Thanks in advance
Code:
'Create pivot table for not complete over 30 days
Set WSo3 = Sheets.Add(after:=Sheets(Sheets.Count))
WSo3.Name = "Piv Over 30"
' Create the Pivot Table from the existing Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSo3. _
Cells(1, 1), TableName:="Over30")
' Turn off updating while building the table
PT.ManualUpdate = True
' Set up the row, column & filterfields
PT.AddFields RowFields:=Array("Brand", "BU"), PageFields:=Array("Days Overdue", "Certification - Status", "Person - Franchise ID")
'set filters
Set PF = WSo3.PivotTables("Over30").PivotFields("Person - Franchise ID")
PF.EnableMultiplePageItems = True
PF.PivotItems("Yes - 3rd Party").Visible = False
PF.PivotItems("Yes - Excluded").Visible = False
Set PF2 = WSo3.PivotTables("Over30").PivotFields("Certification - Status")
PF2.EnableMultiplePageItems = True
PF2.PivotItems("Complete").Visible = False
PF2.PivotItems("Not Yet Complete").Visible = True
'need to filter over 30 only here
' Set up the data fields
With PT.PivotFields("Person - Username")
.Orientation = xlDataField
.Function = xlCount
.Position = 1
End With
' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True