Invalid Procedure Call or Argument VBA Error - 2016 vs 2013?

Madmartigan

New Member
Joined
Sep 13, 2017
Messages
1
I recorded a macro in 2016 and it works fine. Two other users on 2016 have also used it with no issues. I sent the file to a user on Excel 2013 and they get an Invalid Procedure Call or Argument error. I even tried recording the same macro from their computer and it gives me the same error only in a different place. Leads me to believe 2013 can't hack what I'm trying to do but 2016 can? I'm a beginner with VBA.

Here is most of the code:

Code:
End With
    With Sheets("VBAK")
        .Range("E1").copy _
        Destination:=.Range("E3")
        Dim LR As Long
        LR = ActiveSheet.UsedRange.Rows.Count
        Range("E3").AutoFill Destination:=Range("E3:E" & LR)
    End With
    Cells.Select
    Cells.EntireColumn.AutoFit
    ActiveWorkbook.Names.Add Name:="DynamicPivot", RefersToR1C1:= _
        "=OFFSET(VBAK!R2C1,0,0,COUNTA(VBAK!R2C1:R100000C1),5)"
    ActiveWorkbook.Names("DynamicPivot").Comment = ""
    Range("G3").Select
    ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:="DynamicPivot", _
        Version:=6)
    Range("G4").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    Range("A3").Select
End Sub

When I hit the debugger, the section that is highlighted is:

Code:
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:="DynamicPivot", _
        Version:=6)

I've tried researching and I don't think it's a single quotes issue because I don't have spaces in the names. also, I don't see anywhere in my workbook where Excel would be trying to create a new Pivot table named PivotTable1 when one already exists. I just don't get why this works fine in 2016, but not 2013. I'm trying to get this user on 2016 to see if this all goes away, but we are in a time crunch and I might not have that much time. TIA for the help.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top