Automatically Create Named Ranges with Dynamic Names

Question

I want to select a range of cells in a column, automatically create a named range with the selected cells which incorporates a number into the name, and then do the same for a column that is 5 columns over. Here is what I have so far but it doesn't work:

Sub AddNamesMacro() Dim startingrow As Integer startingrow = 1 Dim endingrow As Integer endingrow = 1000 For i = 0 To 100 Step 4 Worksheets("TTMIS").Range(Cells(startingrow, i + 1), Cells(endingrow, i + 1)).Select ThisWorkbook.Names.Add Name:="IS_AccountNames_Year" & i, RefersTo:=Selection.Address(External:=True) Next End Sub

When I run it, then for any i, I do get the named ranges the way I want them; however, in the Name Manager each named range which resulted from the macro has the same thing for the "Value" and the "Refers To" column. For instance the "Refers To" column will say '[Filename]TTMIS'!$A$1:A$1000 and the "Value" Column will say '[Filename]TTMIS'!$A$1:A$1000 instead of listing out the values

Then see this for why Worksheets("TTMIS").Range(Cells(startingrow, i + 1), Cells(endingrow, i + 1) is problematic (unqualified Cells). — BigBen
– BigBen, Commented Mar 22, 2021 at 21:36
=Selection.Address(External:=True) returns a string of the address. You want: = "=" & Selection.Address(External:=True) to create a formula. — Scott Craner
– Scott Craner, Commented Mar 22, 2021 at 21:38

Scott Craner · Accepted Answer · 2021-03-22 21:51:38Z

As per all the comments:

Sub AddNamesMacro() Dim startingrow As Long startingrow = 1 Dim endingrow As Long endingrow = 1000 Dim rng As Range Dim i As Long For i = 0 To 100 Step 4 With Worksheets("TTMIS") Set rng = .Range(.Cells(startingrow, i + 1), .Cells(endingrow, i + 1)) End With ThisWorkbook.Names.Add Name:="IS_AccountNames_Year" & i, RefersTo:= "=" & rng.Address(1, 1, xlA1, 1) Next End Sub

One more suggestion:

To allow for better sorting in the names manager I would use:

Name:="IS_AccountNames_Year" & Format(i,"000")

To make all the numbers 3 digits.

Collectives™ on Stack Overflow

Automatically Create Named Ranges with Dynamic Names

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related