Excel 2010 vba not working in Excel 2012

Question

I have vba code that creates a pivot table automatically from some data that is copied from another workbook to the current workbook. When I run it on my machine, it is fine. Another person has no problems, either, but one woman is getting an error about "Run-time error '5': Invalid procedure call or argument" and it highlights my code for

"ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
  "WorkRange", Version:=xlPivotTableVersion14).CreatePivotTable _
  TableDestination:=ActiveSheet.Cells(3,1), TableName:="PivotTable2", DefaultVersion _ 
  :=xlPivotTableVersion14

My code copies the new data sheet and renames it every time to "Call Data - Date Time" and creates a new pivot table on a new sheet every time, so I don't think it is the table name causing issues, especially since it runs fine for me. She is the only one having issues. Any ideas?

Here is all of the code:

Sub GeneratePivot()
'
' Macro11 Macro
'
    Dim myDate As Date, aDate
    myDate = Date + 7 - Weekday(Date)
    aDate = Format(myDate, "mm.dd.yyyy")

    Dim LValue As Date
    LValue = Now

    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

    ActiveWorkbook.Names.Add Name:="WorkRange", RefersTo:=Selection
    Range("C5").Select
    Application.Goto Reference:="WorkRange"
    Sheets.Add
    ActiveSheet.Name = "SummaryData " & Format(DateTime.Now, "MM.dd.yy hh.mm.ss")

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "WorkRange", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:=ActiveSheet.Cells(3, 1), TableName:="PivotTable2", DefaultVersion _
        :=xlPivotTableVersion14
    ActiveSheet.Select
    Cells(3, 1).Select

    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Month")
        .Orientation = xlColumnField
        .Position = 1
    End With

    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Site/Location")
        .Orientation = xlRowField
        .Position = 1
    End With

    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Called Number")
        .Orientation = xlRowField
    End With

    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Called Number"), "Count of Called Number", xlCount

    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Site/Location")
        .PivotItems("#N/A").Visible = False
    End With

    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

End Sub

So I updated the suspect lines to:

Dim PC As PivotCache
Dim pt As PivotTable

Set PC = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "WorkRange", Version:=xlPivotTableVersionCurrent)

Set pt = PTCache.CreatePivotTable _
        (TableDestination:=ActiveSheet.Cells(3, 1), _
        TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersionCurrent)

PC.CreatePivotTable TableDestination:=ActiveSheet.Cells(3, 1), TableName:="PivotTable1", DefaultVersion _ :=xlPivotTableVersionCurrent

And it failes on Set PC. I also tried xlPivotTableVersion15, but that failed, as well.

Answer 1

I think the problem is because of the version of the PivotTable namely this section:
Version:=xlPivotTableVersion14
Version14 is for Excel 2010. I'm not sure what the version number is for 2012.
Try changing it to:
DefaultVersion:=xlPivotTableVersionCurrent and see if it runs.

Here is a list (taken from here: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlpivottableversionlist(v=office.15).aspx) with the available versions. I don't see Excel 2012 on there, so your mileage may vary:

xlPivotTableVersion2000 refers to Excel 2000
xlPivotTableVersion10 refers to Excel 2002
xlPivotTableVersion11 refers to Excel 2003
xlPivotTableVersion12 refers to Excel 2007
xlPivotTableVersion14 refers to Excel 2010
xlPivotTableVersion15 refers to Excel 2013
xlPivotTableVersionCurrent refers to Provided only for backward compatibility