hippy
02-27-2009, 02:21 PM
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.
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
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
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Fields data type is text, no empty fields. I am at a loss.
Hippy
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.
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
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
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Fields data type is text, no empty fields. I am at a loss.
Hippy