I recorded a pivot table and have code like this in it. However, each day I
run it against a different file and the Pivot Items are different.
1st day
With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Problem")
.PivotItems("Upgrade").Visible = False
.PivotItems("Slow Speed").Visible = False
.PivotItems("Cannot Create Customer Account").Visible = False
2nd day
With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Problem")
.PivotItems("Out of Service").Visible = False
.PivotItems("Slow Speed").Visible = False
.PivotItems("Account Disabled").Visible = False

The macro aborts any time an entry is not there (Upgrade) and again when
there is a new entry (Out of Service)

So how do you create a pivot table that will run no matter what the data is?
This is the lines after the above.
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Problem")
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Cluster")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Diagnosis")
.Orientation = xlRowField
.Position = 3
End With

ActiveSheet.PivotTables("PivotTable5").PivotFields ("Cluster").Orientation = _
xlHidden

Any help would be appreciated.

Thanks,

Ron

Ron,
The trouble with recording macros is that the results can be far more
literal than you want them to be. I find it very useful as a tool to generate
approximately what I want. I then spend time generalising them.

the subrouting below will not do what you want either, but it may give yoiu
the clues necessary to complete the job for yourself.

Sub Pivot_table_example()
Dim thisPT As Integer
Dim thisPF As Integer
Dim thisPI As Integer

For thisPT = 1 To ActiveSheet.PivotTables.Count
Debug.Print ActiveSheet.PivotTables(thisPT)
For thisPF = 1 To ActiveSheet.PivotTables(thisPT).PivotFields.Count
Debug.Print " " &
ActiveSheet.PivotTables(thisPT).PivotFields(thisPF )
For thisPI = 1 To
ActiveSheet.PivotTables(thisPT).PivotFields(thisPF ).PivotItems.Count
Debug.Print " " &
ActiveSheet.PivotTables(thisPT).PivotFields(thisPF ).PivotItems(thisPI)
Select Case
ActiveSheet.PivotTables(thisPT).PivotFields(thisPF ).PivotItems(thisPI)
Case "Upgrade", "Slow Speed" 'and any of the others you set
invisible

ActiveSheet.PivotTables(thisPT).PivotFields(thisPF ).PivotItems(thisPI).Visible = False
Case Else
'whatever you do with those that are visible
End Select
Next thisPI
Next thisPF
Next thisPT

End Sub

"Ron" wrote:

I recorded a pivot table and have code like this in it. However, each day I
run it against a different file and the Pivot Items are different.
1st day
With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Problem")
.PivotItems("Upgrade").Visible = False
.PivotItems("Slow Speed").Visible = False
.PivotItems("Cannot Create Customer Account").Visible = False
2nd day
With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Problem")
.PivotItems("Out of Service").Visible = False
.PivotItems("Slow Speed").Visible = False
.PivotItems("Account Disabled").Visible = False

The macro aborts any time an entry is not there (Upgrade) and again when
there is a new entry (Out of Service)

So how do you create a pivot table that will run no matter what the data is?
This is the lines after the above.
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Problem")
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Cluster")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Diagnosis")
.Orientation = xlRowField
.Position = 3
End With

ActiveSheet.PivotTables("PivotTable5").PivotFields ("Cluster").Orientation = _
xlHidden

Any help would be appreciated.

Thanks,

Ron

I appreciate that Lionel. I'm playing with it to see if I can get it to do
what I need.

Thanks for your input.

Ron

"Lionel H" wrote:

Ron,
The trouble with recording macros is that the results can be far more
literal than you want them to be. I find it very useful as a tool to generate
approximately what I want. I then spend time generalising them.

the subrouting below will not do what you want either, but it may give yoiu
the clues necessary to complete the job for yourself.

Sub Pivot_table_example()
Dim thisPT As Integer
Dim thisPF As Integer
Dim thisPI As Integer

For thisPT = 1 To ActiveSheet.PivotTables.Count
Debug.Print ActiveSheet.PivotTables(thisPT)
For thisPF = 1 To ActiveSheet.PivotTables(thisPT).PivotFields.Count
Debug.Print " " &
ActiveSheet.PivotTables(thisPT).PivotFields(thisPF )
For thisPI = 1 To
ActiveSheet.PivotTables(thisPT).PivotFields(thisPF ).PivotItems.Count
Debug.Print " " &
ActiveSheet.PivotTables(thisPT).PivotFields(thisPF ).PivotItems(thisPI)
Select Case
ActiveSheet.PivotTables(thisPT).PivotFields(thisPF ).PivotItems(thisPI)
Case "Upgrade", "Slow Speed" 'and any of the others you set
invisible

ActiveSheet.PivotTables(thisPT).PivotFields(thisPF ).PivotItems(thisPI).Visible = False
Case Else
'whatever you do with those that are visible
End Select
Next thisPI
Next thisPF
Next thisPT

End Sub

"Ron" wrote:

I recorded a pivot table and have code like this in it. However, each day I
run it against a different file and the Pivot Items are different.
1st day
With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Problem")
.PivotItems("Upgrade").Visible = False
.PivotItems("Slow Speed").Visible = False
.PivotItems("Cannot Create Customer Account").Visible = False
2nd day
With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Problem")
.PivotItems("Out of Service").Visible = False
.PivotItems("Slow Speed").Visible = False
.PivotItems("Account Disabled").Visible = False

The macro aborts any time an entry is not there (Upgrade) and again when
there is a new entry (Out of Service)

So how do you create a pivot table that will run no matter what the data is?
This is the lines after the above.
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Problem")
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Cluster")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Diagnosis")
.Orientation = xlRowField
.Position = 3
End With

ActiveSheet.PivotTables("PivotTable5").PivotFields ("Cluster").Orientation = _
xlHidden

Any help would be appreciated.

Thanks,

Ron