I have searched high and low for the instructions on how to create new Pivot Tables in Progress.
I have used the COM Object Viewer looking at my XL5EN32.OLB but cannot find the exact Pivot Table Creation things that my Macro Code provides.
Can anyone help with this or post a list of Pivot Table calls??
I am about to give up on this as I am running out of hair to pull out.
Below here is what the Macro Outputs:
I can select and add a sheet.
I need the ActiveWorkbook.PivotCaches.Create ... stuff
Has anyone done it - I'd really love to see your sample.
Thanks!!
Range("A1").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"BacklogDetail!R1C1:R4691C23", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet4").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cust Group")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Year")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Bucket")
.Orientation = xlColumnField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Amt Due(k)"), "Sum of Amt Due(k)", xlSum
Range("A17").Select
Sheets("BacklogDetail").Select
ActiveWorkbook.Worksheets("Sheet4").PivotTables("PivotTable1").PivotCache. _
CreatePivotTable TableDestination:="Sheet4!R15C1", TableName:="PivotTable2" _
, DefaultVersion:=xlPivotTableVersion10
Sheets("Sheet4").Select
Cells(15, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Supplier")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Year")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Bucket")
.Orientation = xlColumnField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Amt Due(k)"), "Sum of Amt Due(k)", xlSum
End Sub
I have used the COM Object Viewer looking at my XL5EN32.OLB but cannot find the exact Pivot Table Creation things that my Macro Code provides.
Can anyone help with this or post a list of Pivot Table calls??
I am about to give up on this as I am running out of hair to pull out.
Below here is what the Macro Outputs:
I can select and add a sheet.
I need the ActiveWorkbook.PivotCaches.Create ... stuff
Has anyone done it - I'd really love to see your sample.
Thanks!!
Range("A1").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"BacklogDetail!R1C1:R4691C23", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet4").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cust Group")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Year")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Bucket")
.Orientation = xlColumnField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Amt Due(k)"), "Sum of Amt Due(k)", xlSum
Range("A17").Select
Sheets("BacklogDetail").Select
ActiveWorkbook.Worksheets("Sheet4").PivotTables("PivotTable1").PivotCache. _
CreatePivotTable TableDestination:="Sheet4!R15C1", TableName:="PivotTable2" _
, DefaultVersion:=xlPivotTableVersion10
Sheets("Sheet4").Select
Cells(15, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Supplier")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Year")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Bucket")
.Orientation = xlColumnField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Amt Due(k)"), "Sum of Amt Due(k)", xlSum
End Sub