CODEWITHSUNDEEP
excelvbanamed-ranges
Ganap
Ganap

Reputation: 1

VBA - Naming multiple ranges according to cell values

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

Upvotes: 0

Views: 264

Answers (1)

Super Symmetry
Super Symmetry

Reputation: 2875

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

Upvotes: 1

Related Questions