Excel 2013 Creating a pivot table with vba

0
votes

I have a macro that creates a pivot table. It has worked great for year but not users are moving to Excel 2013 and it is not working. The one twist is that the range of data can change from user to user or time to time depending on a query they run. I tried to modify the code to create the pivot table but I'm getting a syntax error.

Recorded Code:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Results!R2C1:R633C25", Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion15

Modified Code using variable to determine the last row and column:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Results!R2C1:R" & MyRow & "C" & MyCol, _ Version:=xlPivotTableVersion15.CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion15

Any suggestions on what is wrong with the modified code would be greatly appreciated. Thanks for your help

1
excelsyntax-errorpivot-tablevba

1 Answers

0
votes

No longer an issue. The user got a new copy of the macro and it started working again. thanks.