Hi Igor
in XL2007 the code would look something like
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R9C3", Version:=xlPivotTableVersion12).CreatePivotTable
_
TableDestination:="", TableName:="PivotTable3", DefaultVersion:= _
xlPivotTableVersion12
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Year")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Product")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField
ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Value"), "Sum of Value", xlSum
For XL2003 the code would be
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet2!R1C1:R9C3").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Year")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Product")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField
ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Value"), "Sum of Value", xlSum
End Sub
So the difference is that when you create your code in XL2007 (using macro
recorder for example), then just edit the generated code to change
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase
to
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase
and change
DefaultVersion:= _
xlPivotTableVersion12
to
DefaultVersion:= _
xlPivotTableVersion10
--
Regards
Roger Govier
IgorM said:
Hi
How to programmatically create an older version of pivottable in Excel
2007?
Kind regards
IgorM
__________ Information from ESET Smart Security, version of virus
signature database 4798 (20100122) __________
The message was checked by ESET Smart Security.
http://www.eset.com
__________ Information from ESET Smart Security, version of virus signature database 4799 (20100123) __________
The message was checked by ESET Smart Security.
http://www.eset.com