Using VBA to rename Pivot Table Query
 

(Dave This is copied from response I made to your earlier
reply to my query of yesterday I am reposting it here so
that others may have opportunity to help me further, as I
have doubts that many of them are likkely to gpo as far as
a second level expansion to look for a question to answer)

Dave,

Thanks so very very much for your response. I think it
gets close to what I'm after, however perhaps I'm still
doing something not quite right as when I run this
procedure my PivotTable1 has its name changed to
PivotTable_2, rather than the copy. And even if I change
PivotTable1's name to PivotTable_Base it still changes
it's rather than the new table's name to PivotTable_2.

I would appreciate any further thoughts from you or others.

Many thanks again for your response.
Ron
-----Original Message-----
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