Creating a name in a macro with a dynamic range

  • Thread starter Thread starter Eric G.
  • Start date Start date
E

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
 
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.)
 
Back
Top