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:
When I hit the debugger, the section that is highlighted is:
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.
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.