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

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:=xlDatab ase, _
SourceData:="Sheet1!R25C10:R30C12").CreatePivotTab le 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


--
Peter81
------------------------------------------------------------------------
Peter81's Profile: http://www.excelforum.com/member.php...o&userid=25353
View this thread: http://www.excelforum.com/showthread...hreadid=531446

It works for me - Thanks for your help

"Peter81" wrote:


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:=xlDatab ase, _
SourceData:="Sheet1!R25C10:R30C12").CreatePivotTab le 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


--
Peter81
------------------------------------------------------------------------
Peter81's Profile: http://www.excelforum.com/member.php...o&userid=25353
View this thread: http://www.excelforum.com/showthread...hreadid=531446