Is it possible to create named ranges based on tab names programatically?

**adrianodl** · 08-20-2012, 06:32 AM

Hi there,
I'm trying to create named ranges automatically based on the tab name, since I have more than 10 tabs in my doc.
Each tab has exactly the same format, so a script that creates a named range based on A10:b20 on every tab would be ok.

The named range would be called "tabAtable1", "tabAtable2","tabBtable1","tabBtable2" and so on and so forth since each tab has more than one table.

Any leads would be appreciated.

Cheers,

Adri

**Jakobshavn** · 08-20-2012, 07:57 AM

Adapt this to your needs:

Sub WhatsInAName()
Dim s As Worksheet
For Each s In Worksheets
    s.Range("A1:B10").Name = s.Name & "TABLE1"
    s.Range("D10:D20").Name = s.Name & "TABLE2"
Next
End Sub

**adrianodl** · 08-20-2012, 06:50 PM

Hey Jakobshavn, thank you so much!

**Cutter** · 08-20-2012, 08:56 PM

If you are satisfied with the solution(s) provided, please remember to mark your thread as Solved.

New quick method:
Select Thread Tools (above first post on page) -> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
Go to the first post
Click edit
Click Go Advanced
Just below the word "Title" you will see a dropdown with the words "No prefix".
Change to "Solved"
Click Save

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.