Name Range across multiple (200+) sheets

AndreaBG

New Member
Joined
Mar 15, 2020
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
VBA Code:
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:

VBA Code:
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
 
Upvote 0
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).
 

Attachments

  • 2020-03-15 (3).png
    2020-03-15 (3).png
    92.7 KB · Views: 13
  • 2020-03-15 (2).png
    2020-03-15 (2).png
    68 KB · Views: 12
Upvote 0
Try this one:

VBA Code:
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
 
Upvote 0
Should I fill in the input box as it runs or just keep letting it run?
 
Upvote 0
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
 
Upvote 0
Give this a try and see if it solves your issue

VBA Code:
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
 
Upvote 0
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?
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top