View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Dave Peterson[_3_] Dave Peterson[_3_] is offline
external usenet poster
 
Posts: 2,824
Default Using VBA to rename Pivot Table Query

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