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").PivotField s("Source").CurrentPage
= _
"P. YR"

Any help appreciated

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=569783

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("A 3"), _
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").PivotField s("Source").CurrentPage
= _
"P. YR"

Any help appreciated

VBA Noob




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Thanks Debra

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=569783