loop through visible pivotfields , copy to new sheet

[hippy]

I wonder if anyone has can help? I would like to loop the field items and repot on those that have been set to visible. So far the code does loop through items past those for .visible false, copy the first visible item to new book. When the loop gets to the second visible item and error is reported.

I do not understadn the error. I wonder if anyone can help me with the error? Can what I want to do be achieved?

Error 1004
Unable to get the pivotitems property of the pivot table class.

'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
[VBA]
Dim PivItem As PivotItem

For Each PivItem In PT.PivotFields("Service Line").PivotItems
Ctr = Ctr + 1
PT.PivotFields("Service Line") = PivItem.Name
PT.ManualUpdate = False
PT.ManualUpdate = True
' Create a new blank workbook with one worksheet
Set WBN = Workbooks.Add(xlWBATWorksheet)
Set WSR = WBN.Worksheets(1)
WSR.Name = PivItem.Name
' Set up Title for Report
With WSR.[A1]
.Value = "Top 5 Stores in the " & PivItem.Name & " Region"
.Font.Size = 14
End With
' Copy the pivot table data to row 3 of the report sheet
' Use offset to eliminate the page & title rows of the pivot table
PT.TableRange2.Offset(3, 0).Copy
WSR.[A3].PasteSpecial Paste:=xlPasteValuesAndNumberFormats
LastRow = WSR.Cells(65536, 1).End(xlUp).Row
WSR.Cells(LastRow, 1).Value = "Top 5 Total"
' Do some basic formatting
' Autofit columns, bold the headings, right-align
WSR.Range(WSR.Range("A2"), WSR.Cells(LastRow, 3)).Columns.AutoFit
Range("A3").EntireRow.Font.Bold = True
Range("A3").EntireRow.HorizontalAlignment = xlRight
Range("A3").HorizontalAlignment = xlLeft
Range("B3").Value = "Revenue"
Range("A2").Select
Next PivItem
[/VBA]
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Fields data type is text, no empty fields. I am at a loss.

Hippy

[Bob Phillips]

That is because you rename the pivout field here

[vba]

Dim PivItem As PivotItem

For Each PivItem In PT.PivotFields("Service Line").PivotItems
Ctr = Ctr + 1
PT.PivotFields("Service Line") = PivItem.Name '<<<<<<<<<<<<<<<<<<
[/vba]

so it isn't called 'Service Line' second time around.

[hippy]

Thank you for the reply XLD.

I guess I am remedial.

I think you are telling me that I need to change the name each time, though I am not certain.

Could you elaborate a little please?

[Bob Phillips]

No, I am not telling you anything, I am just informing you of where the problem arises and why. I don't know enough of what you are doing/trying to do to tell you what you should be doing.

Personally I have no idea why you are changing any of the field names, but presumably you know why.

[hippy]

Thanks XLD.

[mdmackillop]

Hi Hippy,
Just for info., when you post code, use the green VBA button to format the code as shown.
Regards
MD