Pivot Macro Bug

[matt_the_hat]

I have recorded a macro to create a really basic pivot table but when I run the macro I get a 'Run Time Error 5' message. When I try to debug this is where the error is:


ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Property!R1C1:R971C32", Version:=6).CreatePivotTable TableDestination:= _
"Sheet14!R3C1", TableName:="PivotTable2", DefaultVersion:=6

FYI -For Source Data I use '"Ctrl-A" to select all as the data range will change each time.

Any help would be much appreciated!
Thanks

 

[RoryA]

If you try and run that after recording it, it will try and put a pivot table on top of the original you created while recording, and use the same name for it. That won't work.

 

[matt_the_hat]

If you try and run that after recording it, it will try and put a pivot table on top of the original you created while recording, and use the same name for it. That won't work.

I have tried with new data sets / new file and still get the error message.

 

[RoryA]

If you don't have a sheet14 in the workbook , you will get that error (since that's the destination sheet name supplied). It will also only work on XL2016 or later.

 

[matt_the_hat]

If you don't have a sheet14 in the workbook , you will get that error (since that's the destination sheet name supplied). It will also only work on XL2016 or later.

Thanks. Any idea why the code would say sheet 14? The workbook that I recorded the macro in only had 4 open tabs
In future, each time I need to use the macro, there will be 4 open tabs already. If I change the code to 'sheet5' might that work?

 

[RoryA]

Only if the sheet is always called Sheet5. Recorded code is always very literal - I'd suggest using a worksheet variable that you assign the destination sheet to, then use TableDestination:=ws.range("A3") for example.