pivottable name in a macro

[NEvans009]

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:=xlDatabase, 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

 

[Peter81]

try something like this:

Dim wrksht As Worksheet
Dim pivtble As PivotTable
Dim pivotCounter As Integer

pivotCounter = 0

For Each wrksht In ActiveWorkbook.Worksheets

For Each pivtble In wrksht.PivotTables

If Right(pivtble.Name, 1) > pivotCounter Then
pivotCounter = Right(pivtble.Name, 1)
End If

Next pivtble

Next wrksht

pivotCounter = pivotCounter + 1


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:="Sheet1!R25C10:R30C12").CreatePivotTable TableDestination:=
_
"[Book2]Sheet1!R36C10", TableName:="PivotTable" & pivotCounter,
DefaultVersion:= _
xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable" & pivotCounter).AddFields
RowFields:="a"
ActiveSheet.PivotTables("PivotTable" &
pivotCounter).PivotFields("c").Orientation = _
xlDataField

Pete

 

[NEvans009]

It works for me - Thanks for your help