mikeymike123
New Member
- Joined
- Mar 26, 2024
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
I want to filter the pivot, excluding rows where Facility Type contains 'Biological' OR 'Physical'. See code that does seem to work. Also xlOr doesnt seem to work either.
' Create PivotTable
Set sitePivotTable = sitePivotSheet.PivotTables.Add(PivotCache:=sitePivotCache, TableDestination:=sitePivotSheet.Range("A1"), TableName:=siteName & "_PivotTable")
' Set the PivotTable fields
Set sitePtField = sitePivotTable.PivotFields("Site Name")
sitePtField.Orientation = xlRowField
' Define the filter field for "Site Name"
Set sitePtField = sitePivotTable.PivotFields("Site Name")
sitePtField.Orientation = xlPageField
sitePtField.CurrentPage = siteName ' Set current page
Set sitePtField = sitePivotTable.PivotFields("Site Category")
sitePtField.Orientation = xlRowField
Set sitePtField = sitePivotTable.PivotFields("Facility Type")
sitePtField.Orientation = xlRowField
' Filter the "Facility Type" PivotField to exclude "Biological"
'sitePivotTable.PivotFields("Facility Type").PivotFilters.Add Type:=xlCaptionDoesNotContain, Value1:="Biological"
' Filter the "Facility Type" PivotField to exclude "Physical"
'sitePivotTable.PivotFields("Facility Type").PivotFilters.Add Type:=xlCaptionDoesNotContain, Value1:="Physical"
' Filter the "Facility Type" PivotField to exclude rows containing "Biological"
sitePtField.PivotFilters.Add2 Type:=xlCaptionDoesNotContain, Value1:="Biological"
' Filter the "Facility Type" PivotField again to exclude rows containing "Physical"
sitePtField.PivotFilters.Add2 Type:=xlCaptionDoesNotContain, Value1:="Physical"
' Create PivotTable
Set sitePivotTable = sitePivotSheet.PivotTables.Add(PivotCache:=sitePivotCache, TableDestination:=sitePivotSheet.Range("A1"), TableName:=siteName & "_PivotTable")
' Set the PivotTable fields
Set sitePtField = sitePivotTable.PivotFields("Site Name")
sitePtField.Orientation = xlRowField
' Define the filter field for "Site Name"
Set sitePtField = sitePivotTable.PivotFields("Site Name")
sitePtField.Orientation = xlPageField
sitePtField.CurrentPage = siteName ' Set current page
Set sitePtField = sitePivotTable.PivotFields("Site Category")
sitePtField.Orientation = xlRowField
Set sitePtField = sitePivotTable.PivotFields("Facility Type")
sitePtField.Orientation = xlRowField
' Filter the "Facility Type" PivotField to exclude "Biological"
'sitePivotTable.PivotFields("Facility Type").PivotFilters.Add Type:=xlCaptionDoesNotContain, Value1:="Biological"
' Filter the "Facility Type" PivotField to exclude "Physical"
'sitePivotTable.PivotFields("Facility Type").PivotFilters.Add Type:=xlCaptionDoesNotContain, Value1:="Physical"
' Filter the "Facility Type" PivotField to exclude rows containing "Biological"
sitePtField.PivotFilters.Add2 Type:=xlCaptionDoesNotContain, Value1:="Biological"
' Filter the "Facility Type" PivotField again to exclude rows containing "Physical"
sitePtField.PivotFilters.Add2 Type:=xlCaptionDoesNotContain, Value1:="Physical"