Copying Pivot help !!

[VBA Noob]

Hi,

I've recorded adding one pivot table and then copying it 4 times and
changing some info.

The code below remembers the old Pivot Table no 11. How do I get it to
use the next un-used Pivot table no ??

ActiveSheet.PivotTables("PivotTable11").PivotFields("Source").CurrentPage
= _
"P. YR"

Any help appreciated

VBA Noob

 

[Debra Dalgleish]

You can use variables to identify each pivot table as you create it. For
example:

'=============================
Sub CopyPivot()
Dim ws1 As Worksheet
Dim pc As PivotCache
Dim pt1 As PivotTable
Dim pt2 As PivotTable

Set ws1 = Worksheets("Sheet1")
Set pc = ActiveWorkbook.PivotCaches.Add(xlDatabase, _
SourceData:="PivotData")
Set pt1 = pc.CreatePivotTable(TableDestination:=ws1.Range("A3"), _
TableName:="SalesPivot1")

With pt1
.AddFields RowFields:="Region"
.PivotFields("Units").Orientation = xlDataField
With .PivotFields("Item")
.Orientation = xlPageField
.Position = 1
End With
End With

pt1.TableRange2.Copy Destination:=ws1.Range("F1")
Set pt2 = ws1.Range("F1").PivotTable
pt2.PivotFields("Item").CurrentPage = "Desk"

End Sub
'==============================

 

[VBA Noob]

Thanks Debra

VBA Noob