Runtime Error 5 Sometimes in VBA Creating PivotTab

Question

I have been recording/writing a macro in Excel 2016, 64 bit version. I need to create a PivotTable at the end of it. I have tried four different ways on my computer and they all work.

HOWEVER! When I send the file to another user, the PivotTable section always fails and it always returns

Run-time Error 5: Invalid procedure call or argument.

The other user is on a virtual desktop (Windows 2008 R2) and Office 2013, which is the 32 bit version.

I did research this issue, which is why I have so many different bits of code below, but no matter what I do, the other user gets the Run-time Error 5. Again, all of these work as intended on my computer, so I'm assuming there's a version issue, but I admit that I'm guessing at this point. What am I missing?

Macro recorder code:

Range("A1").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
  "Table1", Version:=6).CreatePivotTable TableDestination:="Summary!R1C1", _
  TableName:="PivotTable3", DefaultVersion:=6

Most common fix I found online:

Range("A1").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
  "Table1", Version:=6).CreatePivotTable TableDestination:="'Summary'!R1C1", _
  TableName:="PivotTable3", DefaultVersion:=6

Second most common fix I found online:

Range("A1").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
  "Table1", Version:=6).CreatePivotTable TableDestination:=ActiveWorkbook.Worksheets("Summary").Range("$A$1"), _
  TableName:="PivotTable3", DefaultVersion:=6

I even tried to cheat by importing an existing PivotTable into a new workbook and then updating where the pivot gets its data, but I still get a Run-time Error 5.

ActiveSheet.PivotTables("PivotTable3").ChangePivotCache ActiveWorkbook. _
  PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Table1", Version:=6)

Answer 1

I had the same issue, and I realized that you should used the last version of your PivotTableCache available on Excel 2013, which is 15 when you change and / or create the Pivot Cache.

Something like this:

Dim Wkb As Workbook, Wks As Worksheet, rDB As Range
Dim sSheet as String, sPvt as String

sSheet = "[SheetName]"
sPvt = "[PivotTableName]"

Set Wkb = ThisWorkbook
Set Wks = Wkb.Worksheets(sSheet)
Set rDB = Wks.Range("A1:I2500")

WksI.PivotTables(sPvt).ChangePivotCache Wkb. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rDB, _
        Version:=xlPivotTableVersion15) '' This is the line you need to change
'' Excel 2010 Version:=xlPivotTableVersion14
'' Excel 2013 Version:=xlPivotTableVersion15