Solved: Cycling through Pivot Table Items

[Slicemahn]

Hello Everyone,

What I am trying to do is to cycle through all the pivot items and create individual sheets for each. The twist is that I have two pivot fields in the page area: The Call Centre has three items and the Type has two items.
I have written some code but it only provides the first item of each pivot field. I really appreciate your thoughts and suggestions.

[VBA]Dim PTCache As PivotCache
Dim PT As PivotTable
Dim LastRow As Long
Dim LastCol As Long
Dim rngSource As Range
Dim Wks As Worksheet
Dim Wkr As Worksheet
Dim c As Integer
Dim EndRow As Long
Dim CallCentrePvtItem As Integer
Dim TypePvtItem As Integer
Dim CallCentre As String
Dim CType As String

EndRow = Sheets("RAW").Cells(65536, 2).End(xlUp).Row

Set rngSource = Range("B5 :G" & EndRow)


Application.ScreenUpdating = False

'Delete PivotSheet if it exists
On Error Resume Next
Application.DisplayAlerts = False

Sheets("Pivotdata").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Create a Pivot Cache
Set PTCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, SourceData:=rngSource)


'Add new worksheet
Set Wks = Worksheets.Add
Wks.Name = "Pivotdata"

'Create the pivot table from the cache
Set PT = PTCache.CreatePivotTable( _
TableDestination:=Wks.Range("A1"), _
TableName:="RCINational")

With PT
'Add fields
.AddFields RowFields:=Array("LOB", "CSC Name"), PageFields:=Array("Type", "Call_Centre")
.PivotFields("Calls Tracked").Orientation = xlDataField
.PivotFields("Resolved").Orientation = xlDataField
.CalculatedFields.Add "%Resolved Calls", "=Resolved/'Calls Tracked'", True
.PivotFields("%Resolved Calls").Orientation = xlDataField
.PivotFields("Sum of %Resolved Calls").NumberFormat = "0.0%"
.PivotFields("LOB").PivotItems("Core Cellular") = "Wireless"
.PivotFields("Type").PivotItems("Inbound Call") = "Calls"
.TableRange1.EntireColumn.AutoFit
.DataPivotField.Orientation = xlColumnField
.DataPivotField.PivotItems("Sum of Calls Tracked").Caption = "Tracked Calls"
.DataPivotField.PivotItems("Sum of Resolved").Caption = "Resolved Calls"
.DataPivotField.PivotItems("Sum of %Resolved Calls").Caption = "% of Resolved Calls"


' Set up loop to generate report through the three call centres and by types
' Moncton is the only centre in which there will be both Emails and calls for type
' The loop will create a new page for each Call Centre

For CallCentrePvtItem = 1 To .PivotFields("Call_Centre").PivotItems.Count
For TypePvtItem = 1 To .PivotFields("Type").PivotItems.Count
.PivotFields("Call_Centre").CurrentPage = .PivotFields("Call_Centre").PivotItems(CallCentrePvtItem).Name
.PivotFields("Type").CurrentPage = .PivotFields("Type").PivotItems(TypePvtItem).Name
.ManualUpdate = False
.ManualUpdate = True


Set Wkr = ActiveWorkbook.Worksheets.Add
Wkr.Name = .PivotFields("Call_Centre").Name & .PivotFields("Type").Name
.TableRange2.Copy
Wkr.Range("A6").PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
Wkr.Range("A6").Select
Next TypePvtItem
Next CallCentrePvtItem
End With

End Sub[/VBA]

[lucas]

You can ignore this suggestion if it's completely off base. It just seems to me that the pivot table is created from data on the sheet..why not create the individual sheets from that same data instead of trying to work with the pivot table?

[Slicemahn]

Actually Lucas, the data comes from an Access tables. Both sources are brought within the sheet via MSQuery. What you stated makes sense however the sources I am dealing with are extensive over 34K rows.