change named range within macro

[JulieM]

hello, I hope I'm not attempting the impossible here!

I'm using range names to make sure that a macro jumps to work from th
correct place, no matter where the current select point is when th
macro starts. The macro then uses relative movements to perform th
required actions on that area of the sheet(s).

However, (one of) the end result of the macro is insertion of data
which needs to become the start point on the next occasion that th
macro is run. For example, if the macro started at Sheet1!A1 last time
the next run should start from Sheet1!A2.

At the moment, I am having to remember to change the range name afte
running the macro, so I would like to incorporate changing it as th
last section of the macro, but am failing miserably.

Even if I try to just record the steps as a separate macro to copy
paste into the main macro, it doesn't work. The nearest success I'v
had just extended the range to include the new cell, which doesn't hel
because the relative movement is still towards (eg) Sheet1!B1 rathe
than Sheet1!B2.

Any help much appreciated, I can post relevant parts of the curren
macro if that helps?
Thank you

 

[mudraker]

JulieM


Try adding this code to your macro

I assumed your named range refers to a single cell
I assumed you want to move the named range down 1 row

You will need to replace nstart with your named range name



r = Sheet1.Range("nstart").Row + 1
ActiveWorkbook.Names.Add Name:="nStart", _
RefersToR1C1:="=Sheet1!R" & r & "C1

 

[Tom Ogilvy]

Range("MyName").Offset(1,0).Name = "MyName"

 

[JulieM]

Thank you kindly, both.

I think Tom's looks as though it will do what I was doing - extendin
the range rather than changing it?

I will try them both tonight and let you know.

Thanks again, much appreciated. :

 

[Tom Ogilvy]

As written, my code changes the range to the next cell below (which appeared
to be what you wanted). If you actually want to extend it, it would be

Range("MyName").Resize(Range("MyName").Rows.count + 1).Name = "MyName"

 

[JulieM]

Hi Tom
You were absolutely spot on, thank you.
Juli