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]