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

Start [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) perhaps. — BigBen, Mar 22 '21 at 21:36
Then see [this](https://stackoverflow.com/questions/8047943/excel-vba-getting-range-from-an-inactive-sheet) for why `Worksheets("TTMIS").Range(Cells(startingrow, i + 1), Cells(endingrow, i + 1)` is problematic (unqualified `Cells`). — BigBen, Mar 22 '21 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, Mar 22 '21 at 21:38

Scott Craner · Answer 1 · 2021-03-22T21:51:38.650

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.

Automatically Create Named Ranges with Dynamic Names

1 Answers1