I have recorded a macro to create a pivottable. The macro includes a
pivottable name which is specific to the table e.g. TableName:="PivotTable1"
as below

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Sheet1!R25C10:R30C12").CreatePivotTable TableDestination:= _
"[Book2]Sheet1!R36C10", TableName:="PivotTable1", DefaultVersion:= _
xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="a"
ActiveSheet.PivotTables("PivotTable1").PivotFields ("c").Orientation = _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False

Can anybody tell me how to make this macro allocate the next pivottable
number to use in the spreadsheet rather than re-use "PivotTable1" each time
which seems to cause an error when the table already exists ?

Thanks

Nigel