• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to create same nameranges in different named sheets?

N

ngc2392

New Member
Hello, I just came around this site looking for solution to my problem in excel. So, as the title implies...i have 50+ sheets (car plates) and on each sheet there is a range of cells (12 of them for whole year) where I enter the values. Ranges are identical through all the sheets. So, how can I easily (or faster than manual entering ranges...) name those ranges across my entire workbook?
If solution is somewhere on this site, please point me there.
Thanks
 
Not sure if this is what you want:
Select your range on any sheet and, using the name box, type in a name (eg. myRng):

1729644298668.png

then go into Name Manager and edit this Name by removing the sheet reference but keeping the exclamation mark:

from this:

1729644395538.png

to this:

1729644476186.png

Then on any sheet, you can use the name, eg.:
=SUM(myRng)
and it will refer to the range on that sheet.

Is that what you were hoping for?
 
A VBA Solution

Code: 
Option Explicit


Sub NameRange()
    Dim ws As Worksheet
    Dim i As Long
    Dim rng As Range
    Set rng = Range("A1:L1")     'Change range to your specific range
    i = 1
    For Each ws In Worksheets
        ws.rng.Name = "MyRange" & i ' Change the range name to your specifiec name
        i = i + 1
    Next ws


End Sub
 
Last edited:
Amendment doesn't work here:

1729647197203.png

Maybe you're after:
Code: 
Sub NameRange()
Dim ws As Worksheet
For Each ws In Worksheets
  ws.Range("A1:L1").Name = "'" & ws.Name & "'!YourRange"    ' Change the range name to your specifiec name
Next ws
End Sub

1729647407247.png
 
Last edited:
It is a kbit late for you by now but, if you need to do the same setting up a new workbook, you could set up a template sheet with defined names at workbook level. Then copy that sheet each time you need a working sheet. The names will come across with local scope.

Something else that can be useful on occasion, is to define a 3D named range across the group of contiguous sheets.
name = Sheet1:Sheet9!$A$1:$L$1
= SUM(name)
has always aggregated across sheets, but now
= VSTACK(name)
will build a table on a summary sheet to support further analysis.
 
thank you guys...i will try p45cal suggestion first. I am helping friend with this task so...and I am learning smth new along the way :) So, I don't need to sum ranges across the workbook, I just need names of the month (january, february etc) to create hyperlinks so that person will not need to scroll way down there to december and he will just click to cell with links.
btw, p45cal...is there a way that can I edit your code that I can enter all 12 nameranges (months) in one go?

@Peter: if all the sheets would have the same name, yes, I could copy one and paste it...but here I have 50+ different names.
 
ok, code worked, also for all 12 nameranges. thanks guys. Now, just a little more tweaking and editing hyperlinks on each sheet...is there also a way to code it? like...namerange january is for a1 on every sheet, february for b2 etc etc.
 
Last edited:
If you're working on the same subset of sheets in you workbook, you can restrict the code to work only on that subset:
Code: 
For each ws in Sheets(Array("Sheet1", "Sheet2", "Another Sheet", "ThisSheet", "ThatSheet"))
January for all sheets is A1:
Code: 
ws.Range("A1").Name = "'" & ws.Name & "'!January"
February for B2:
Code: 
ws.Range("B2").Name = "'" & ws.Name & "'!February"
etc.
All these can be within the same loop:
Code: 
For each ws in Sheets(Array("Sheet1", "Sheet2", "Another Sheet", "ThisSheet", "ThatSheet"))
    ws.Range("A1").Name = "'" & ws.Name & "'!January"
    ws.Range("B2").Name = "'" & ws.Name & "'!February"
    'etc...
Next ws
 
p45cal said:
If you're working on the same subset of sheets in you workbook, you can restrict the code to work only on that subset:
Code: 
For each ws in Sheets(Array("Sheet1", "Sheet2", "Another Sheet", "ThisSheet", "ThatSheet"))
January for all sheets is A1:
Code: 
ws.Range("A1").Name = "'" & ws.Name & "'!January"
February for B2:
Code: 
ws.Range("B2").Name = "'" & ws.Name & "'!February"
etc.
All these can be within the same loop:
Code: 
For each ws in Sheets(Array("Sheet1", "Sheet2", "Another Sheet", "ThisSheet", "ThatSheet"))
    ws.Range("A1").Name = "'" & ws.Name & "'!January"
    ws.Range("B2").Name = "'" & ws.Name & "'!February"
    'etc...
Next ws
Click to expand...
Sorry, I am not that familiar with vba things here...
This code will put hyperlink entry in a1 for the namerange january in each specific sheet with different name?
 
ngc2392 said:
Sorry, I am not that familiar with vba things here...
This code will put hyperlink entry in a1 for the namerange january in each specific sheet with different name?
Click to expand...
Oh, I see, maybe not. I'll look at this in a few hours; you want someone clicking cell A1 to be sent to the named range January on the same sheet?
 
For both adding named ranges and adding hyperlinks, something along the lines of:
Code: 
For Each ws In Sheets(Array("Sheet15", "Sheet16", "Sheet8"))
    ws.Range("A14:A28").Name = "'" & ws.Name & "'!January"
    ws.Range("B14:B28").Name = "'" & ws.Name & "'!February"
    'etc...
    ws.Hyperlinks.Add Anchor:=ws.Range("A1"), Address:="", SubAddress:="January", TextToDisplay:="January"
    ws.Hyperlinks.Add Anchor:=ws.Range("B1"), Address:="", SubAddress:="February", TextToDisplay:="February"
    'etc...
Next ws
 
You must log in or register to reply here.
Back
Top