Change Pivot Table Name without Select 1

[Zwicky]

Hi Everyone,

I am trying to change a pivot table name in excel vba code within a worksheet_change function
(Private Sub WorkSheet_Change(ByVal Target As Excel.Range)which does not allow you to use the select method for another worksheet.

I have tried:

Sheets("Pivot").Range("B12").PivotTableWizard TableName:="Pivot"

and

With Sheets("Pivot").Range("B12").PivotTableWizard(TableName:="Pivot")
End With

where B12 is one of the cells in the pivot table.

I get the error 438: Object doesn't support this property or method

Any ideas?

Thanks in advance to anyone who can help!

 

[SkipVought]

Hi,

Sheets("Pivot").Range("B12").PivotTable.Name="Pivot"

Skip,
[sub]
[red][/red]
[/sub]