Need help with automating adding Names ranges
Glad it helped.
--
Regards,
Tom Ogilvy
"rpw" wrote in message
...
Hi Tom,
Thanks for the quick response. I ended up using Offset and Resize in my
original formula after seeing how you used it in your code.
Here's my modified code (that works!):
'Re-validate the list names and ranges
Worksheets("Categories").Activate
Worksheets("Categories").Range("B1").Select
For MainIntCtr = 1 To 10
On Error Resume Next
Selection.Offset(0, 1).Range("A1").Select
strName = Selection
Names(strName).Delete
Names.Add Name:=strName, RefersTo:=Selection.Offset(27,
0).Resize(29, 1)
Next MainIntCtr
Thanks again for your quick help. Sorry that my thanks are coming late,
but
my first attempt didn't post for some reason.
Rick...
"Tom Ogilvy" wrote:
Sub AAAA()
For Each cell In Worksheets("Categories").Range("B1:L1")
ThisWorkbook.Names.Add Name:=cell.Value, _
RefersTo:=cell.Offset(27, 0).Resize(29, 1)
Next
End Sub
--
Regards,
Tom Ogilvy
"rpw" wrote in message
...
Hi,
I've managed to get to the point where the name is added to the Names
list,
but when I run the code all of the added names have the same range
reference.
If I step through the code, I see that a name is added (e.g.
New_List).
I
check the Names/Define in Excel and the range is correct (e.g.
B28:B56).
I
step through the next name and it is added correctly, but now both
have
the
same range (e.g.C28:C56). If I run the code a second time then the
row
numbers are offset (e.g. C29:C57).
Here is the portion of the code that is giving me problems (I think):
'Re-validate the list names and ranges
Worksheets("Categories").Activate
Worksheets("Categories").Range("B1").Select
For SubIntCtr = 1 To 10
On Error Resume Next
Selection.Offset(0, 1).Range("A1").Select
strName = Selection
Names.Add Name:=strName, RefersTo:="=Categories!a28:a52"
Next SubIntCtr
It's taken me hours to get it this far along and I'm stuck now so I
could
use some help. Any help is much appreciated.
tia :-)
--
rpw
|