This seemed to work ok for me:
Option Explicit
Sub testme()
With ActiveSheet
.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel
Selection.Copy
Application.Goto Reference:="Target_Region"
End With
With ActiveSheet
.Paste
Application.CutCopyMode = False
.PivotTables(.PivotTables.Count).Name = "PivotTable_2"
End With
End Sub
The last pivottable added is .pivottables.count.
Ron McCormick wrote:
I wish to copy a pivot table report and name
it "PivotTable_2". How do I do this programmatically? I
tried recording a macro and came up with the following:
ActiveSheet.PivotTables("PivotTable1").PivotSelect "",
xlDataAndLabel
Selection.Copy
Application.Goto Reference:="Target_Region"
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.PivotTables("PivotTable8").Name
= "PivotTable_2"
The problem is that next time I run the procedure the
Pivot Table to be named "PivotTable_2" is "PivotTable9".
How do I get the table to be "PivotTable8" each time? (If
I knew that I wouldn't actually need to rename it
as "PivotTable_2"!).
TIA
Ron
--
Dave Peterson