I am new to VBA and am trying to write a macro that will create a pivot table. I need to filter various fields and have tried using PivotFilters.Add and PivotItems to only let certain things through...sometimes it works, but other times it throws errors. The "Resource name" field is giving me problems. I need to only show the resource names that begin with "*TBD" and exclude those that contain "ATG" in the name.


Sub CreatePivot()
Dim objTable As PivotTable, objField As PivotField

ActiveWorkbook.Sheets("CP Monthly Data").Select
Range("A1").Select
Set objTable = Sheet1.PivotTableWizard
objTable.Name = "Resource Requests"
objTable.InGridDropZones = True
objTable.RowAxisLayout xlTabularRow

' Specify row and column fields.
Set objField = objTable.PivotFields("Company name")
objField.Orientation = xlRowField
objField.Position = 1

Set objField = objTable.PivotFields("Probability Status")
objField.Orientation = xlRowField
objField.Position = 2
objField.PivotItems("X - Lost - 0%").Visible = False <<<<< It works here
objField.PivotItems("X - On Hold - 0%").Visible = False <<<<<And it works here
objField.AutoSort xlDescending, "Probability Status"

Set objField = objTable.PivotFields("Project")
objField.Orientation = xlRowField
objField.Position = 3

Set objField = objTable.PivotFields("Project manager")
objField.Orientation = xlRowField
objField.Position = 4

Set objField = objTable.PivotFields("Resource name")
objField.Orientation = xlRowField
objField.Position = 5
objField.PivotFilters.Add xlCaptionContains, Value1:="TBD" <<<<<<<Application defined or object defined error
objField.PivotFilters.Add xlCaptionDoesNotContain, Value1:="ATG" <<<<<<<Application defined or object defined error
objField.AutoSort xlAscending, "Resource name" <<<<<<<This works fine, too.

' Specify data fields.
Set objField = objTable.PivotFields("June, 2012")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "##"
objField.Caption = "June"

' Specify a page field.
Set objField = objTable.PivotFields("Workgroup Name")
objField.Orientation = xlPageField
objField.PivotItems("ATG").Visible = False <<<<<<This works
objField.PivotItems("India - ATG").Visible = False <<<<<<So does this
objField.PivotItems("India - Managed Middleware").Visible = False <<<<<<And this

Application.DisplayAlerts = True
End Sub

Any ideas on how to fix this? I'm using Excel 2010 and have spent hours searching for this, and nothing I've tried has worked. Thanks in advance for any help!!