PDA

View Full Version : Pivot Table code that worked in 2003 not working with 2010



wnazzaro
03-14-2012, 11:20 AM
In 2003, this code executes
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Worksheets("Resource Summary").Range(Cells(1, 1), Cells(EndRow, EndCol))).CreatePivotTable _
TableDestination:="'[Resource Calclulator.xls]Pivot Table'!R3C1", _
TableName:="FTEPivotTable", _
DefaultVersion:=xlPivotTableVersion10
It fails in 2010. I tried changing .Add to .Create, but I still get an error.
Does someone know what has changed that is causing my failure?

Kenneth Hobs
03-14-2012, 08:17 PM
I don't use PT's much. If you post a short example file, it is easier to help.

mancubus
03-15-2012, 12:04 AM
hi.

you can download an example file here:
http://gallery.technet.microsoft.com/Excel-2010-Code-Example-c1be6ed3


in the same file macro recorder produces:


ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Employees!R1C1:R19C8", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="[PivotTablesAndCharts.xlsm]Sheet1!R18C2", TableName:= _
"PivotTable4", DefaultVersion:=xlPivotTableVersion14
With ActiveSheet.PivotTables("PivotTable4").PivotFields("DEPT")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("LOCATION")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("SALARY"), "Sum of SALARY", xlSum

Kenneth Hobs
03-15-2012, 05:44 AM
You might want to check that you set the proper SourceData.

msgbox Worksheets("Resource Summary").Range(Cells(1, 1), Cells(EndRow, EndCol)).Address

F8 stepping would show you the value of EndRow and EndCol.

Bob Phillips
03-15-2012, 06:07 AM
I don't use PT's much.

What! I am speechless :wot

Aflatoon
03-16-2012, 06:45 AM
This part:
Worksheets("Resource Summary").Range(Cells(1, 1), Cells(EndRow, EndCol))
needs to be:
Worksheets("Resource Summary").Range(Worksheets("Resource Summary").Cells(1, 1), Worksheets("Resource Summary").Cells(EndRow, EndCol))
or use a With...End With block.