VBA - Naming multiple ranges according to cell values

Question

I'm trying to name intervals in every worksheet according to the content of a specific cell in each worksheet. I've looked up many ways of naming the ranges, whoever none uses the content of a cell as a name. This is what I have (but it doesn't work):

Sub NameRanges()

Dim WS_Count As Integer
Dim I As Integer
Dim r As String

     WS_Count = ActiveWorkbook.Worksheets.Count


     For I = 5 To WS_Count

        r = Worksheets(I).Range("A2")
        Names.Add Name:="r", RefersTo:=Worksheets(I).Range("B6:B10000")


     Next I
End Sub

Names.Add Name:=r? Variables don't belong inside quotes.

BigBen
– BigBen

2020-07-09 21:40:00 +00:00
Commented Jul 9, 2020 at 21:40 — BigBen
– BigBen, Commented Jul 9, 2020 at 21:40

Super Symmetry · Accepted Answer · 2020-07-14 14:41:18Z

1

To name a sheet simply use its .Name property like this:

Sub NameSheets()
    Dim i As Integer
    With ActiveWorkbook
        For i = 5 To .Sheets.Count
            .Sheets(i).Name = .Sheets(i).Range("A2").Value
        Next i
    End with
End Sub

edited Jul 14, 2020 at 14:41

answered Jul 9, 2020 at 22:01

Super Symmetry

2,8851 gold badge9 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ganap Over a year ago

Thank you @SuperSymmetry, however it's still not working. I get a message saying that there's a syntax error regarding this part: Worksheets(i).Range("B6:B10000").Name = Worksheets(i).Range("A2").Value

Super Symmetry Over a year ago

Most likely because you don't have valid names in A2 of these worksheet. When it gives you the error, what name are you trying to give this range?

Ganap Over a year ago

I'm actually trying to name 100 different sheets in the same workbook, each with a different name (in cell A2 of each sheet).

Super Symmetry Over a year ago

Why would you then say "I'm trying to name intervals in every worksheet". And why would you name (or use) a prcedure NameRanges. and make the name ReferTo Range("B6:B10000"). And why would you say in the question "I've looked up many ways of naming the ranges". Please make sure you understand what you're saying and write what you mean, otherwise, you're wasting the time of people that are trying to help you. Anyway, I updated my answer

Ganap Over a year ago

First of all, thank you for your help, I really appreciate that! I misspoke on my latest comment, sorry about that. I have to name the ranges "B6:B10000" in every sheet according to the name in cell "A2" of that specific sheet. So it's the ranges I have to name, not the sheets

Collectives™ on Stack Overflow

VBA - Naming multiple ranges according to cell values

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related