Copying Pivot

[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 wrote:
> 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
Contextures
http://www.contextures.com/tiptech.html

[VBA Noob]

Thanks Debra

VBA Noob