Can you please have a short look on the attached code, basically the macro recorder is creating like this.
For me the problem is that it adds the new sheet as Sheet2, but on the worksheet where I would like to apply the macro, sometimes already has such one or the default would come as Sheet3 or Sheet4 etc. And in such cases error message comes.
Is there maybe some best practice to solve this? I assume at the Sheet.Add also a sheet name should be defined, and the pivot would be placed on that
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Report_of_WC_Capacity!R1C1:R65536C17", Version:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:="PivotTable1" _