Creating a name in a macro with a dynamic range

[Eric G.]

I want to be able to automate copying a formula to a range of cells.
Although I have figured out how to define a name in Excel '97 and 2000
using a macro, the problem is that the macro actually imbeds the
initial range of the name in the code (eg:
RefersToR1C1:="=Sheet1!R1C8:R26C8"). Therefore, the next time I run
the macro, Excel always assumes that the range I want to copy formulas
to is rows 1-26 of column 8. If I have data in rows 1-50 of column 8
for example, Excel does not copy the formula to rows 27-50.

I'm an old Lotus 123 user. In 123 you could create a macro which
allowed you to select a named range, and then resize the range by
using a combination of the "end" key and the "arrow" keys to identify
the new range to which the name belongs. You could then copy a
formula to all of the cells in the range. You could instruct 123 to
resize the range each time you ran the macro, and therefore were able
to copy a formula to all of the current cells in the range regardless
of the size of the previous range. This is what I want to do in
Excel.

Any suggestions would be greatly appreciated.

Thanks.

Eric

 

[Dave Peterson]

Are you using this range name somewhere else?

If no, then I'd just determine the range on the fly:

Dim LastRow as long

with worksheets("Sheet1")
lastrow = .cells(.rows.count,8).end(xlup).row
.range("A1:X" & lastrow).copy _
destination:=worksheets("sheet2").range(whereyou'repasting)
end with

If you really need a dynamic range, you can record a macro when you do it
manually following the instructions at Debra Dalgleish's dynamic range name web
page:

http://www.contextures.com/xlNames01.html#Dynamic

(In my example, I copied A1 thru X(Lastrow of G). Probably not what you want.)