I have a VBA code to clean up a report which summarizes the data in a pivot table. I only have rows & multiple values but when the macro is ran it only includes the rows. Some reason the values are not pulling in. Code below
'Insert Pivot Table
'Declare Variables
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
'Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Pivot").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "Pivot"
Application.DisplayAlerts = True
Set PSheet = Worksheets("Pivot")
Set DSheet = Worksheets("Data")
'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange)
'CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), _
TableName:="SalesPivotTable")
'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")
'Insert Row Fields
With PTable.PivotFields("Transaction Date Time")
.Orientation = xlRowField
.Position = 1
End With
'Insert Data Field
With PTable.PivotTables("SalesPivotTable")
.PivotFields ("Gross Revenue")
.Orientation = xlDataField
.Position = 1
.Description = "Sum of Gross Revenue"
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Revenue "
End With
With PTable.PivotTables("SalesPivotTable")
.PivotFields ("Refunds")
.Orientation = xlDataField
.Position = 2
.Description = "Sum of Refunds"
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Refunds "
End With
'Format Pivot Table
ActiveSheet.PTable("SalesPivotTable").ShowTableStyleRowStripes = True
This is what I get:
This is what is should look like:
Thank you in advance for any help
'Insert Pivot Table
'Declare Variables
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
'Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Pivot").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "Pivot"
Application.DisplayAlerts = True
Set PSheet = Worksheets("Pivot")
Set DSheet = Worksheets("Data")
'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange)
'CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), _
TableName:="SalesPivotTable")
'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")
'Insert Row Fields
With PTable.PivotFields("Transaction Date Time")
.Orientation = xlRowField
.Position = 1
End With
'Insert Data Field
With PTable.PivotTables("SalesPivotTable")
.PivotFields ("Gross Revenue")
.Orientation = xlDataField
.Position = 1
.Description = "Sum of Gross Revenue"
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Revenue "
End With
With PTable.PivotTables("SalesPivotTable")
.PivotFields ("Refunds")
.Orientation = xlDataField
.Position = 2
.Description = "Sum of Refunds"
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Refunds "
End With
'Format Pivot Table
ActiveSheet.PTable("SalesPivotTable").ShowTableStyleRowStripes = True
This is what I get:
This is what is should look like:
Thank you in advance for any help