How can I get the name of the Pivot Table using Macros
 

I am copying a Pivot Table and pasting it within the same sheet using a
Macro. However when i try to modify the properties of the newly pasted pivot
table, i am thrown a error which says that the Pivot table does not exist.
The Table properties on the copied pivot diaplay the name of the Pivot to be
"PivotTable12" but depending on how many times i have run this macro without
closing XL this # can vary.

What i want to do from within the Macro is to know of the name of the Pivot
Table so that i can refer to it when making the changes to the settings.

How can I get the name of the Pivot Table using Macros
 

If there are only two, then you can refer to it as

Activesheet.PivotTables(2)

or you can use that to get the name

nme = Activesheet.PivotTables(2)

or if you know the name of the original pivot table

for i = 1 to Activesheet.PivotTables.count
set pt = activesheet.PivotTables(i)
if lcase(pt.name) < "pivottable1" then
nme = pt.Name
exit for
end if
Next

--
Regards,
Tom Ogilvy


"Saran" wrote:

I am copying a Pivot Table and pasting it within the same sheet using a
Macro. However when i try to modify the properties of the newly pasted pivot
table, i am thrown a error which says that the Pivot table does not exist.
The Table properties on the copied pivot diaplay the name of the Pivot to be
"PivotTable12" but depending on how many times i have run this macro without
closing XL this # can vary.

What i want to do from within the Macro is to know of the name of the Pivot
Table so that i can refer to it when making the changes to the settings.

How can I get the name of the Pivot Table using Macros
 

Hi Tom,

Thanks for the feedback and your piece of code perfectly well.

Thanks
Saran

"Tom Ogilvy" wrote:

If there are only two, then you can refer to it as

Activesheet.PivotTables(2)

or you can use that to get the name

nme = Activesheet.PivotTables(2)

or if you know the name of the original pivot table

for i = 1 to Activesheet.PivotTables.count
set pt = activesheet.PivotTables(i)
if lcase(pt.name) < "pivottable1" then
nme = pt.Name
exit for
end if
Next

--
Regards,
Tom Ogilvy


"Saran" wrote:

I am copying a Pivot Table and pasting it within the same sheet using a
Macro. However when i try to modify the properties of the newly pasted pivot
table, i am thrown a error which says that the Pivot table does not exist.
The Table properties on the copied pivot diaplay the name of the Pivot to be
"PivotTable12" but depending on how many times i have run this macro without
closing XL this # can vary.

What i want to do from within the Macro is to know of the name of the Pivot
Table so that i can refer to it when making the changes to the settings.