Hi there

I have workbook with the following details.
Sheets are called Data, Final, Report, People.
Data sheet has information imported from access to be used to create a pivot
table.
People sheet contains information on the people contained in the data sheet
(also imported from access).
Report sheet has a combo drop down box so that someone can select a name and
then the idea is they click on a button to generate pivot table and a table
is generated and filtered to show just that one persons data.

I have all the code written to create the pivot table and this works
correctly, but when i add in code to set all pivotitems.visible to equal
false for all but one entry i get an error message.

"Run-time error 1004 - Unable to get the PivotItems property of the
PivotField class."

If i list each individually and then try to set just one to visible = true
it wont complete the list, it just crashed out after the first few are done.

ie.
ActiveSheet.PivotTables("360").PivotFields("FullNa me").PivotItems("Caroline
Knight").Visible = False

I have found an article 114822 that mentions a problem with using the
visible command, but this only relates to Excel version 5.

Here is my code, please note that some lines are commented out where i was
trying different things to get this working.

Sub CreatePivotTable()
Dim PTCache As PivotCache
Dim PT As PivotTable
' Dim Pi As PivotItem
Dim strField As String
Dim x As String
Dim names(200) As String
Application.ScreenUpdating = False
Application.DisplayAlerts = True
' select data
Sheets("Data").Select
Columns("A:H").Select
strField = Selection.Cells(1, 1).Text
' name the list range
Range(Selection, Selection.End(xlDown)).Name = "Items"
' create pivot table from named list
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:="=Items").CreatePivotTable
TableDestination:=Sheets("Report").Range("E4"), _
TableName:="360"
Worksheets("Report").Activate
Set PT = ActiveSheet.PivotTables("360")
With PT
'Add fields
.AddFields RowFields:=Array("Fullname", "Persnum"), _
ColumnFields:="SurveyID"
' Add Data Field
.AddDataField .PivotFields("AnswerResult")
' Change to average
.PivotFields("Count of AnswerResult").Function = xlAverage
.PivotFields("Average of AnswerResult").NumberFormat = "0.0"
End With
Application.ScreenUpdating = True
' remove grand total for rows
ActiveSheet.PivotTables("360").RowGrand = False
' remove blank rows
With ActiveSheet.PivotTables("360").PivotFields("FullNa me")
.PivotItems("(blank)").Visible = False
End With
' remove blank columns
With ActiveSheet.PivotTables("360").PivotFields("Survey ID")
.PivotItems("(blank)").Visible = False
End With
' define value for x
Range("D2").Select
x = ActiveCell.Value
'select the Report sheet
Sheets("Report").Select
' read the names in Col B into the names array
For i = 1 To 200
names(i) = Cells(i, 2).Value
Next i
' Change data in pivot table to the person defined by value x and names array
For i = 2 To 201
kk = "" & names(i) & ""

ActiveSheet.PivotTables("360").PivotFields("FullNa me").PivotItems(kk).Visible
= False
Next i
' With ActiveSheet.PivotTables("360").PivotFields("FullNa me")
'
ActiveSheet.PivotTables("360").PivotFields("FullNa me").PivotItems("Brenton
Clark").Visible = False
'
ActiveSheet.PivotTables("360").PivotFields("FullNa me").PivotItems("Caroline
Knight").Visible = False
'
ActiveSheet.PivotTables("360").PivotFields("FullNa me").PivotItems("Jenny
Abrahall").Visible = False
'
ActiveSheet.PivotTables("360").PivotFields("FullNa me").PivotItems("Jon
Funiciello").Visible = False
'
ActiveSheet.PivotTables("360").PivotFields("FullNa me").PivotItems("Maryann
Pratt").Visible = False
'
ActiveSheet.PivotTables("360").PivotFields("FullNa me").PivotItems("Sonia
Bressey").Visible = False
'
ActiveSheet.PivotTables("360").PivotFields("FullNa me").PivotItems("(blank)").Visible = False
' End With

ActiveSheet.PivotTables("360").PivotFields("FullNa me").PivotItems(x).Visible
= True
'For i = 2 To 201
'If names(i) < x Then
'kk = "" & names(i) & ""
'Worksheets("Report").PivotTables("360").PivotFiel ds("FullName").PivotItems(kk).Visible = False
'End If
'Next i
' correct column widths
Columns("G:O").Select
Selection.ColumnWidth = 13
' close field list box and floating toolbar
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False
End Sub

Any assistance into why this wont work would be great, i am not sure if this
is a bug or a problem with my code.

Thanks

Caroline