VBA - Named Range Using Cell References

[MrSourApple]

I need to create multiple named ranges for a workbook that is updated regularly. Each time the workbook is updated a new worksheet is added and it needs named ranges to make other formulas in the workbook continue to work.

• "Admin" tab: This tab has the name of the new worksheet that will be created in cell E1 (since it changes regularly the creation of the worksheet is macro driven)

• "Named Ranges" tab: This tab has the names that need to be used for the named ranges in cells B2:B29 (B2 is a named range for column B of the new tab, B3 another named range for column C of the new tab...)

So to try and summarize: I want to create named ranges on the new tab, the new tab name is referenced in Admin!E1. The names for the named ranges are listed in 'Named Ranges'!B2:B29.

Here is what I put together, please help (this is just for the first named range not for all):

Sub MakeNamedRanges()
Set UIIRng = Sheets(Sheets("Admin").Range("E1").Value).Range("B:B")

ActiveWorkbook.Names.Add _​

Name:=Sheets(Sheets("Named Ranges").Range("B2").Value), RefersTo:=UIIRng​

End Sub

 

[JLGWhiz]

I need to create multiple named ranges for a workbook that is updated regularly. Each time the workbook is updated a new worksheet is added and it needs named ranges to make other formulas in the workbook continue to work.

• "Admin" tab: This tab has the name of the new worksheet that will be created in cell E1 (since it changes regularly the creation of the worksheet is macro driven)

• "Named Ranges" tab: This tab has the names that need to be used for the named ranges in cells B2:B29 (B2 is a named range for column B of the new tab, B3 another named range for column C of the new tab...)

So to try and summarize: I want to create named ranges on the new tab, the new tab name is referenced in Admin!E1. The names for the named ranges are listed in 'Named Ranges'!B2:B29.

Here is what I put together, please help (this is just for the first named range not for all):

Sub MakeNamedRanges()
Set UIIRng = Sheets(Sheets("Admin").Range("E1").Value).Range("B:B")

ActiveWorkbook.Names.Add _​

Name:=Sheets(Sheets("Named Ranges").Range("B2").Value), RefersTo:=UIIRng​

End Sub

If you are naming entire columns as a 'Named Range', why not just refer to them as Columns("A"), Columns("B"), etc.? Perhaps I have misinterpreted the post.

 

[MrSourApple]

If you are naming entire columns as a 'Named Range', why not just refer to them as Columns("A"), Columns("B"), etc.? Perhaps I have misinterpreted the post.

I don't think I can do that because the ranges located on a different tab than where the names of the named ranges are. There are three tabs being used here:

(1) Admin - cell E1 (Admin!E1) has the name of the new tab
(2) Named Ranges - cells B2:B29 have the names of the ranges I want to use on the new tab
(3) NewTab - This tab is named using a macro, based on Admin!E1

In order to name the ranges on NewTab I need to tell it how to find the tab --- Sheets(Sheets("Admin").range("E1").Value
I add Range("B:B") to the end so it knows which column to choose. Up to this point I think my macro is working.

It seems like my macro is messing up when it gets to adding the names. Any ideas?

 

[JLGWhiz]

You have 28 range names in your list, but it is unclear if you are trying to name 28 columns in a single sheet, or if you are trying to use 28 different names for the same column in 28 different sheets. You need to make your objective a little more understandable.

 

[MrSourApple]

I am trying name 28 columns in a single sheet - each column has a different name. The name of each 'named range' is being pulled from the sheet "Named Ranges". The tab these named ranges will apply to is identified as I mentioned earlier through the 'Admin' tab.

 

[JLGWhiz]

See if this works

Sub rngNames()
Dim sh1 As Worksheet, Sh2 As Worksheet, i As Long, nm As String
Set sh1 = Sheets("Admin")
Set Sh2 = Sheets("Named Ranges")
nm = sh1.Range("E1").Value
    For i = 2 To 28
        Sheets(nm).Names.Add Sh2.Cells(i, 2).Value, Sheets(nm).Columns(i)
    Next
End Sub

 

[MrSourApple]

Thank you JLGWhiz, this worked just as it was supposed to (had to adjust "For i = 2 to 28" to be "2 to 29" because it was cutting off the last named range but it works great. I am not all that familiar with vba and this was a huge help.

 

[MrSourApple]

I actually just noticed this - is there a quick way to make all of the new named ranges have a workbook scope? I didn't realize creating them this way would make them have a scope of the worksheet.