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

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

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

.

I could reproduce it if the pivottable1 were already located on the
range("target_region").

Is it possible that you're copying the pivottable on top of itself (not changing
anything but its name)???

If my pivottable1 were in a different worksheet (or if pivottable1 weren't on
target_region), then the code worked ok for me.

Maybe you could be a little more explicit by avoiding the activesheet.

Option Explicit
Sub testme()

With worksheets("myWksheet")
.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel
Selection.Copy
Application.Goto Reference:="Target_Region"
End With

' With ActiveSheet 'target_region's worksheet
with thisworkbook.names("target_region").referstorange. parent
.Paste
Application.CutCopyMode = False
.PivotTables(.PivotTables.Count).Name = "PivotTable_2"
End With

End Sub

If that's not it, I'm at a loss.

Ron McCormick wrote:

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

.


--

Dave Peterson