Pivot Table Macro Help

[douglascfast]

Anyone,

I need to assign a name to one of my pivot table columns (The column
will move as users change the page items and such)

so I need excel to find the column figure out the length and assign it
a name so that I can use the name in other functions (conditional
formatting)

Here is what I thought would work:

Worksheets("PSA_Tool").Names.Add _
Name:="Doug1", RefersTo:= _
Worksheets("PSA_Tool").PivotTables("Main").PivotSelect "'Lon Prem
Bookings Vs Target'", _
xlDataOnly

I get an error telling me it expects end of statement.

Here are the two statements which do work but do not assign a name like
I wanted to:

Worksheets("PSA_Tool").PivotTables("Main").PivotSelect "'Lon Prem
Bookings Vs Target'", _
xlDataOnly


Worksheets("PSA_Tool").Names.Add Name:="Doug1",
RefersTo:=ActiveArea (Active area gives me a #NA under names)


Any ideas?

Doug

 

[Debra Dalgleish]

Instead of Activearea, use Selection

Anyone,

I need to assign a name to one of my pivot table columns (The column
will move as users change the page items and such)

so I need excel to find the column figure out the length and assign it
a name so that I can use the name in other functions (conditional
formatting)

Here is what I thought would work:

Worksheets("PSA_Tool").Names.Add _
Name:="Doug1", RefersTo:= _
Worksheets("PSA_Tool").PivotTables("Main").PivotSelect "'Lon Prem
Bookings Vs Target'", _
xlDataOnly

I get an error telling me it expects end of statement.

Here are the two statements which do work but do not assign a name like
I wanted to:

Worksheets("PSA_Tool").PivotTables("Main").PivotSelect "'Lon Prem
Bookings Vs Target'", _
xlDataOnly


Worksheets("PSA_Tool").Names.Add Name:="Doug1",
RefersTo:=ActiveArea (Active area gives me a #NA under names)


Any ideas?

Doug