chiragpandya
New Member
- Joined
- Feb 26, 2018
- Messages
- 5
Trying to make the following code work but not able to create the pivot
Any ideas?
Any ideas?
Code:
Sub Pivott()
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Segment Summary").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "Segment Summary"
Application.DisplayAlerts = True
Set PSheet = Worksheets("Segment Summary")
Set DSheet = Worksheets("Working File")
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(9, 1).Resize(LastRow, LastCol)
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="SalesPivotTable")
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Segment")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Cur CSP")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Actual_Sales")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Revenue "
End With
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Actual_Cost")
.Orientation = xlDataField
.Position = 2
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Revenue "
End With
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Actual_Cost_Support")
.Orientation = xlDataField
.Position = 3
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Revenue "
End With
End Sub
Last edited by a moderator: