Same Named Range, Multiple Worksheets - Please Help I'm Going Insane

[_Echo_2]

I have searched everywhere for hours for a solution to this snag I'm running into.

I would like to create a named range that will apply to the same area of cells in the active sheet no matter which sheet is active. I have tried searching relative and absolute named ranges, 3-D named ranges, etc - none of which do what I want.

So here's what I want:

I have a range of cells that have some data, let's say it's A1:C10. That same range contains the same table of data on all worksheets in the workbook (but different values on each sheet). There can be any number of sheets in the workbook, so when I create, move, delete sheets I still want that name to exist and apply to the newly added sheet(s).

One solution I did find was to, every time I make a new sheet, define the name range as =SheetX!$A$1:$C$10. However, I want it to automatically have that range named when the sheet is created, not have to manually do it every time (as I have at least 6 areas on each sheet that are named ranges).

So if I'm on Sheet 6 I want to click the name drop-down list at the top left and select "Named_Range" and it will take me to A1:C10 on Sheet 6. If I'm on Sheet 500 and I select "Named_Range" I want it to take me to A1:C10 on Sheet 500. If I'm on Sheet 500 and I make a new sheet (Sheet 501) inserted after 500, I want to be able to go to Sheet 501, select "Named_Range" and have it select A1:C10 on 501.

How can I do this? It's driving me insane.

I'm using Excel 2016.

Thank you.

 

[wideboydixon]

Alt+F11 to get to the VBA Editor and then locate the ThisWorkbook object and open it. Paste the following:

Private Const rangeName = "RangeName"
Private Const rangeRefersTo = "$A$1:$C$10"
Private Sub Workbook_NewSheet(ByVal Sh As Object)

ActiveWorkbook.Names.Add Sh.Name & "!" & rangeName, "='" & Sh.Name & "'!" & rangeRefersTo

End Sub

Set the appropriate values for rangeName and rangeRefersTo and you're good to go I think.

WBD

 

[marcelocbd]

I don't think you can have multiple ranges with the same name in the same workbook.

Someone may know better than be, but I really think it's not possible.

 

[Eric W]

marcelocbd is right, you can't have the same name for multiple ranges, even if they are on separate sheets. However, you can make it act the way you describe.

Press Alt-F11 to open the VBA editor, double-click the ThisWorkbook item in the menu on the left, and paste this code into the window that opens:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim MyNames, i As Long, wk As String

    MyNames = Array("FirstOne", "$A$1:$C$6", _
                    "SecondOne", "$D$1:$E$6", _
                    "ThirdOne", "$G$5:$G$7")
    
    For i = 0 To UBound(MyNames) Step 2
        wk = "=" & Sh.Name & "!" & MyNames(i + 1)
        ActiveWorkbook.Names.Add Name:=MyNames(i), RefersTo:=wk
    Next i
    
End Sub

Change the MyNames array to have the names and ranges you want. Close the VBA editor with Alt-Q.

Now whenever you change, or add, a sheet, this routine will run and dynamically change the Names to apply to the current sheet. So if you choose a name from the drop down box, it will take you to that range on the current sheet.

Since the ranges will apply to the current sheet only, and change every time you change sheets, you should not use the names in formulas. If you want to do something like that, then you'd need to make the names unique to the workbook, maybe by adding the sheet name, something like:

Name1 - Sheet1
Name1 - Sheet2

Let me know if this helps.

 

[_Echo_2]

marcelocbd is right, you can't have the same name for multiple ranges, even if they are on separate sheets. However, you can make it act the way you describe.

Press Alt-F11 to open the VBA editor, double-click the ThisWorkbook item in the menu on the left, and paste this code into the window that opens:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim MyNames, i As Long, wk As String

    MyNames = Array("FirstOne", "$A$1:$C$6", _
                    "SecondOne", "$D$1:$E$6", _
                    "ThirdOne", "$G$5:$G$7")
    
    For i = 0 To UBound(MyNames) Step 2
        wk = "=" & Sh.Name & "!" & MyNames(i + 1)
        ActiveWorkbook.Names.Add Name:=MyNames(i), RefersTo:=wk
    Next i
    
End Sub

Change the MyNames array to have the names and ranges you want. Close the VBA editor with Alt-Q.

Now whenever you change, or add, a sheet, this routine will run and dynamically change the Names to apply to the current sheet. So if you choose a name from the drop down box, it will take you to that range on the current sheet.

Since the ranges will apply to the current sheet only, and change every time you change sheets, you should not use the names in formulas. If you want to do something like that, then you'd need to make the names unique to the workbook, maybe by adding the sheet name, something like:

Name1 - Sheet1
Name1 - Sheet2

Let me know if this helps.

Thank you all for the help so far!

Eric, you're code works on all sheets I create at the end of the tab list or ones I create through right-clicking a sheet tab -> move or copy -> check create a copy box -> move to end --- with a few exceptions.

If I create a sheet through the second method, I get:

Run-time error '1004':
There's a problem with this formula.
Not trying to type a formula?
When the first character is an equal (=) or minus (-) sign, Excel thinks it's a formula...

Clicking 'Debug' highlights the line of your code

ActiveWorkbook.Names.Add Name:=MyNames(i), RefersTo:=wk

Not sure what the problem is here, but if I just click 'End' or leave the debugger and continue as normal, the named ranges are there.

Also, for some reason, not all the sheets currently in the workbook have the named ranges. The code should work when a sheet is activated but it seems as if some sheets are not recognizing the names or something. If I create copies of the sheets that don't have the named ranges, the ranges are not on the copies either. Not sure why some sheets have the ranges and some don't. Unless the names just aren't showing up in the Name Box on these sheets; which leads me to ask why they show up there on some sheets and not others.

Good start so far though!

 

[Eric W]

Change this line:

wk = "=" & Sh.Name & "!" & MyNames(i + 1)

to

wk = "='" & Sh.Name & "'!" & MyNames(i + 1)

That should fix the first problem. Let me know if the second problem still occurs.

 

[_Echo_2]

Change this line:

wk = "=" & Sh.Name & "!" & MyNames(i + 1)

to

wk = "='" & Sh.Name & "'!" & MyNames(i + 1)

That should fix the first problem. Let me know if the second problem still occurs.

The apostrophe's added fixed the problem on creating a new sheet through copying. Named ranges are also on new sheets made by clicking the + icon on the tab list.

While testing the above, I noticed that the sheets I mentioned that were missing the ranges somehow now have the ranges. Upon saving the file and closing it then reopening it, the named ranges were on all sheets. Any idea why they suddenly appeared? Was it something to do with the error mentioned earlier that was preventing them from showing up?

Anyway, it works now! I greatly appreciate your help - this was driving me crazy for hours yesterday...

 

[Eric W]

I'm not entirely sure why the ranges weren't there, but I suspect it was a side effect of the first problem.

I'm glad it works for you!