Name Range across multiple (200+) sheets

[AndreaBG]

Hi, I have an excel workbook with 200+sheets of data tables. I need to create a named range for each table.

That is for each table, I go in and highlight a range - say A22:S50 and then name it Table1, Table2 in the name box or I can do this for name manager. Is there a way to automate this process, rather than having to do this for 200 or so tables - can I just somehow set this up so that the name box at top shows the lists of tables going down and each one has A22:S50 as the range it refers to.

 

[alansidman]

Option Explicit

Sub RangeName()
    Dim ws As Worksheet
    Dim rng As Range
    Set rng = Range("A22:S50")    'Change to your range
    Dim RangeName As String
    RangeName = "MyNewRange"  'Change to your Range Name
    For Each ws In Worksheets
        ActiveWorkbook.Names.Add Name:=RangeName, RefersTo:=rng
    Next ws
End Sub

A second option for you:

Option Explicit

Sub RangeName()
    Dim ws As Worksheet
    Dim rng As Range
    Set rng = Range("A22:S50")    'Change to your range
    Dim RangeName As String
    RangeName = InputBox("Provide RangeName")
    'RangeName = "MyNewRange"  'Change to your Range Name
    For Each ws In Worksheets
        ActiveWorkbook.Names.Add Name:=RangeName, RefersTo:=rng
    Next ws
End Sub

 

[AndreaBG]

Thanks. This is getting there but not exactly what I was asking. When I run the code you sent, it produces one table called table in the name box that has the correct range. I want it to create a list of tables in the name box as follows table1, table2, table3 (see screenshot below), whereby each of these tables has that same range "A22:S250" as the range but it would be specific to that sheet (see other screenshot below).

 

[alansidman]

Try this one:

Option Explicit

Sub RangeName()
    Dim ws As Worksheet
    Dim rng As Range
    Set rng = Range("A1:S9")    'Change to your range
    Dim RangeName As String
    'RangeName = "MyNewRange"  'Change to your Range Name
    For Each ws In Worksheets
    RangeName = InputBox("Provide RangeName")
        ws.Names.Add Name:=RangeName, RefersTo:=rng
    Next ws
End Sub

 

[AndreaBG]

Should I fill in the input box as it runs or just keep letting it run?

 

[AndreaBG]

I think the first formula you provided above was better. The only issue is that I want it to keep replicating so that there'd be a Table1, Table2, etc. but it only provides a Table1

 

[alansidman]

Give this a try and see if it solves your issue

Option Explicit

Sub RangeName()
    Dim ws As Worksheet
    Dim rng As Range, x As Long, i As Long
    x = ThisWorkbook.Worksheets.Count
    Set rng = Range("A22:S50")    'Change to your range
    Dim RangeName As String
    RangeName = "Table"
    x = 1
    For Each ws In Worksheets
        ws.Names.Add Name:=RangeName & x, RefersTo:=rng
        x = x + 1
    Next ws
End Sub

 

[AndreaBG]

This time each table is coming in name manager but the range value that's being referred to is from table 1. Should the range be set with table in front of it and then code to modify it sequentially using x = x + 1 as you did for table name?

 

[AndreaBG]

 

[AndreaBG]

Above shows each table has that same range coming from Table 1 when it should have the range coming from its own table
(e.g table 10 should be 'Table 10'!$A$22:$S50)