Pivot table code question

[John Menken]

Hi, I'm in Excel 2010 and using the code below to do some pivot table
work. In several of the lines of code the pivot table is identified as
PivotTable6. This worries me because I want it to be able to run on
Pivot tables of the future that will most likely not have that name.
Is there a way that I can modify this code to have it identify the
pivot table correctly without actually using its name? Many thanks.



Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase,
SourceData:= _
"My Calcs!R1C1:R109C23",
Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable6",
DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable6").PivotFields("Mgr E-
mail")
.Orientation = xlRowField
.Position = 1
End With
Range("A9").Select
With ActiveSheet.PivotTables("PivotTable6")
.ColumnGrand = False
.RowGrand = False
End With
Range("A6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

MsgBox "The Macro is paused until you paste the email addresses in
an Outlook email. Click OK when complete"

 

[John Menken]

Never mind.
I answered my own question.
Thanks.

 

[Jim Cone]

X =1
The first pivot table on a sheet is Worksheets("SheetName").PivotTables(X)

X = Worksheets("SheetName").PivotTables.Count
The last pt is Worksheets("SheetName").PivotTables(X)

The pt name is Worksheets("SheetName").PivotTables(X).Name
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html
editorial review of special sort excel add-in (30 ways to sort)






"John Menken" <[email protected]>
wrote in message
news:781e41a7-745d-44d2-a6d3-35b8391aa760@ov1g2000pbb.googlegroups.com...