[JamesE]

I need a macro to locate a named row, insert a new row (immediately above it)
then copy formula from the named row to the newly created row. At the time of
creating the macro, it identifies the paste destination as an absolute
address, rather than relative to the named row. Consequently, it works only
once and subsequently doesn't copy to the newly created row but to the row it
first created which is specifically addressed in the the macro. Maybe there
is a relative cell movement command I could manually insert in the macro like
"UP"? I've tried various "tricks" including naming the new row, but can't
overcome the macro's capture of the original paste address. Can anybody
suggest a solution?

[joel]

Rather than copy the cell get the formula

MyFormula = Range("A6").formula
Range("A5") = Myformula

My

"JamesE" wrote:

I need a macro to locate a named row, insert a new row (immediately above it)
then copy formula from the named row to the newly created row. At the time of
creating the macro, it identifies the paste destination as an absolute
address, rather than relative to the named row. Consequently, it works only
once and subsequently doesn't copy to the newly created row but to the row it
first created which is specifically addressed in the the macro. Maybe there
is a relative cell movement command I could manually insert in the macro like
"UP"? I've tried various "tricks" including naming the new row, but can't
overcome the macro's capture of the original paste address. Can anybody
suggest a solution?

[Mike H]

Hi,

You could re-define the range every time the macro runs as your original row.

ActiveWorkbook.Names.Add Name:="Myrange", RefersToR1C1:="=Sheet1!R11"

The defines a named rang of "MyRange" which applies to row 11.

Mike

"JamesE" wrote:

I need a macro to locate a named row, insert a new row (immediately above it)
then copy formula from the named row to the newly created row. At the time of
creating the macro, it identifies the paste destination as an absolute
address, rather than relative to the named row. Consequently, it works only
once and subsequently doesn't copy to the newly created row but to the row it
first created which is specifically addressed in the the macro. Maybe there
is a relative cell movement command I could manually insert in the macro like
"UP"? I've tried various "tricks" including naming the new row, but can't
overcome the macro's capture of the original paste address. Can anybody
suggest a solution?