Macro to make pivot tables works in 2003 but not 2007

Fool_Working

New Member
Joined
Mar 23, 2006
Messages
31
Hi, I have a macro that makes pivot tables based on changing data. I wrote it in Excel 2003, and it works fine still in 2003.

However, my computer was recently upgraded to Excel 2007. The code errors out whenever it hits the pivot table macro now. I tried re-recording the pivot table build in 2007 and replacing my old code, but it still errors out. Help?

Here is my original pivot table code at the section where the error happens:

Code:
    ActiveSheet.Name = "Detail"
    ActiveCell.SpecialCells(xlLastCell).Select
  
    PivotReference = "'Detail'!R1C1:" & ActiveCell.Address
'Create Pivot Table of Total Costs $
        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        PivotReference).CreatePivotTable TableDestination:= _
        "'Pivot Summary'!R13C1", _
        TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10

Here is my 2007 code, that is still getting the same error:
Code:
  ActiveSheet.Name = "Detail"
    ActiveCell.SpecialCells(xlLastCell).Select
  
    PivotReference = "'Detail'!R1C1:" & ActiveCell.Address
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        PivotReference, Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:="Pivot Summary!R4C4", TableName:="PivotTable1" _
        , DefaultVersion:=xlPivotTableVersion12

There is additional code afterward to set up the rows, columns, values, etc... but it doesn't even get to that point, so I didn't include that code.
Any suggestions?
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Watch MrExcel Video

Forum statistics

Threads
1,118,400
Messages
5,571,905
Members
412,423
Latest member
monbri9931
Top