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]

A little bit of rearranging. I think I have it now.

Option Explicit

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

 

[AndreaBG]

Thank you. There is one last thing - the scope needs to be the workbook - right now it is that individual sheet. How do you change the scope on all of them to be the workbook.

 

[alansidman]

I don't understand your request. Please provide an example of what you mean.

 

[DanteAmor]

Try this

Sub Macro1()
  Dim sh As Worksheet
  For Each sh In Sheets
    ActiveWorkbook.Names.Add "Table" & sh.Index, "=" & sh.Name & "!" & Range("A22:S50").Address
  Next
End Sub

 

[AndreaBG]

@alansidman, when I run your code, scope is each individual worksheet (3.16 snapshot below) for that worksheet so I can only apply that name range within that worksheet.
I want the scope to be the workbook for each name range so I can apply that name range anywhere.
See example where scope is the workbook for all (see 3.15 snapshot below).

@Dante Amor, The Sub Macro works in that scope is the workbook - however, the table names are not appearing in the name box on top.

 

[DanteAmor]

The Sub Macro works in that scope is the workbook - however, the table names are not appearing in the name box on top.

After running the macro, I see all the names created in the name box:

You could do the test in a new book with a couple of sheets.

Sub Macro1()
  Dim sh As Worksheet
  For Each sh In Sheets
    ActiveWorkbook.Names.Add "Table" & sh.Index, "=" & sh.Name & "!$A$22:$S$50"
  Next
End Sub

 

[alansidman]

Looks like Dante has provided a workable solution. I will bow out.