Hello! Since a recent change in excel the following macro works but appears differently. I'm wondering if there's a way to alter the VBA code to create the Pivot table and show subtotals at bottom of group. Thanks!
Sub CreateInvoicePVT_1()
Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim PVT As PivotTable
Dim StartPvt As String
Dim SrcData As String
SrcData = ActiveSheet.Name & "!" & Range("A1:R50000").Address(ReferenceStyle:=xlR1C1)
Set sht = Sheets.Add
StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SrcData)
Set PVT = pvtCache.CreatePivotTable( _
TableDestination:=StartPvt, _
TableName:="PivotTable1")
ActiveCell.Offset(4, 1).Range("A1").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Invoice#")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("RequestAmount"), "Sum of RequestAmount", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("InvoiceDate")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.Name = "INVOICE PIVOT"
End Sub
Sub CreateInvoicePVT_1()
Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim PVT As PivotTable
Dim StartPvt As String
Dim SrcData As String
SrcData = ActiveSheet.Name & "!" & Range("A1:R50000").Address(ReferenceStyle:=xlR1C1)
Set sht = Sheets.Add
StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SrcData)
Set PVT = pvtCache.CreatePivotTable( _
TableDestination:=StartPvt, _
TableName:="PivotTable1")
ActiveCell.Offset(4, 1).Range("A1").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Invoice#")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("RequestAmount"), "Sum of RequestAmount", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("InvoiceDate")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.Name = "INVOICE PIVOT"
End Sub