Dear David,
Just carefully replace,
RefersToR1C1:="=Sheet1!R2C1:R12C6"
with
RefersTo:=Selection
You have four ranges, so do the replace four times for each range.
Regards,
Kishan Reddy, K
On Aug 21, 1:04 am, David Stubbs <davidmstu...@gmail.com> wrote:
> Hello everyone,
>
> I am trying to write a macro to name four ranges in a sheet. The
> ranges will have the same width every time, and the ranges below the
> two at the top with always be separated by 1 blank row but more or
> less rows. I thought it would work by using:
>
> Range(Selection, Selection.End(xlDown)).Select
> Range(Selection, Selection.End(xlToRight)).Select
>
> To select one range, then name it and then move to the next range and
> repeat. But when I record the macro, the cell references for the
> ranges are hard coded in, so when I run it on new data with a
> different number of rows, the named ranges do not correspond to the
> new data.
>
> The full code I have is below, and a file call “dynamic naming.xlsm”
> has been loaded to the group site.
>
> Any thoughts much appreciated
>
> Thanks
>
> David
>
> -------------------------------------
>
> Sub NamingMacro()
>
> ActiveCell.Offset(1, 0).Range("A1").Select
> Range(Selection, Selection.End(xlDown)).Select
> Range(Selection, Selection.End(xlToRight)).Select
> ActiveWorkbook.Names.Add Name:="WorldIm",
> RefersToR1C1:="=Sheet1!R2C1:R12C6"
> ActiveWorkbook.Names("WorldIm").Comment = ""
> ActiveCell.Select
> Selection.End(xlDown).Select
> Selection.End(xlDown).Select
>
> Range(Selection, Selection.End(xlDown)).Select
> Range(Selection, Selection.End(xlToRight)).Select
> ActiveWorkbook.Names.Add Name:="WorldEx",
> RefersToR1C1:="=Sheet1!R14C1:R26C6"
> ActiveWorkbook.Names("WorldEx").Comment = ""
> ActiveCell.Select
> Selection.End(xlToRight).Select
> Selection.End(xlUp).Select
> Selection.End(xlUp).Select
> Selection.End(xlToRight).Select
> ActiveCell.Offset(1, 0).Range("A1").Select
>
> ActiveCell.Select
> Range(Selection, Selection.End(xlToRight)).Select
> Range(Selection, Selection.End(xlDown)).Select
> ActiveWorkbook.Names.Add Name:="USIm",
> RefersToR1C1:="=Sheet1!R2C10:R9C15"
> ActiveWorkbook.Names("USIm").Comment = ""
> ActiveCell.Offset(1, 0).Range("A1").Select
> Selection.End(xlDown).Select
> Selection.End(xlDown).Select
>
> ActiveCell.Select
> Range(Selection, Selection.End(xlDown)).Select
> Range(Selection, Selection.End(xlToRight)).Select
> ActiveWorkbook.Names.Add Name:="USEx",
> RefersToR1C1:="=Sheet1!R11C10:R18C15"
> ActiveWorkbook.Names("USEx").Comment = ""
>
> End Sub
>
> ------------------------------------
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
