Slicemahn
12-24-2007, 10:01 AM
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.
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
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.
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