CODEWITHSUNDEEP
vbaexcelpivot-table
TBoulz
TBoulz

Reputation: 351

Creating Pivot Table to Include Decimals in the xlSum number format

I am using some vba code to create a pivot table from scratch. It works almost perfectly, so I'm not looking to overhaul the code.

There is just one issue...the numbers from the data include decimals, but once I create the pivot table, the numbers in the table do not include the decimal places, including the grand total.

I need the table to show the decimals from the original data, especially in the grand total field. After searching the site, I havent been able to find anything down to this level of detail on pivot table VBA. Here is the VBA I'm currently using.

Sub Pivot_Table()

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("Cash AR195").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "Cash AR195"
Application.DisplayAlerts = True
Set PSheet = Worksheets("Cash AR195")
Set DSheet = Worksheets("AR195 Detail")

'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(2, 2), _
TableName:="AR195Totals")

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="AR195Totals")

'Insert Row Fields
With ActiveSheet.PivotTables("AR195Totals").PivotFields("Batch #")
.Orientation = xlRowField
.Position = 1
.Name = "Batch # "
End With

'Insert Data Field
With ActiveSheet.PivotTables("AR195Totals").PivotFields("Amount")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Amount "
End With

'Format Pivot Table
ActiveSheet.PivotTables("AR195Totals").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("AR195Totals").TableStyle2 = "PivotStyleMedium9"

End Sub

I'll also include some images of the data fields, and the pivot table after I run the VBA.

The Original Data

Pivot Table Data

This might seem minor, but I absolutely need to include the decimal. Its probably something very simple I missed, but I'm struggling to get the correct number format to include the decimal in the final product (pivot table).

Thank you for any help in advance, I appreciate it greatly!

Upvotes: 0

Views: 1157

Answers (1)

TBoulz
TBoulz

Reputation: 351

Okay I cant believe I missed this...but I solved my own question shortly after posting.

I just added the decimal to the .NumberFormat = field. Here is the update....

'Insert Data Field
With ActiveSheet.PivotTables("AR195Totals").PivotFields("Amount")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = "#,###.##"
.Name = "Amount "
End With

I'm in awe I didnt notice this earlier...thanks anyway everybody!

Upvotes: 1

Related Questions