How can I name worksheets in Excel according to named ranges?

D

Dominique

I've got a spreadsheet which I need to replicate regularly that contains
about 10 spreadsheets. Each spreadsheet makes use of a different named range
in that spreadsheet. Is there a way to set the worksheet tab name according
to a named range that I've already defined in the spreadsheet? All help much
appreciated. Thank you.
 
R

Richard Buttrey

I've got a spreadsheet which I need to replicate regularly that contains
about 10 spreadsheets. Each spreadsheet makes use of a different named range
in that spreadsheet. Is there a way to set the worksheet tab name according
to a named range that I've already defined in the spreadsheet? All help much
appreciated. Thank you.
Click to expand...

ActiveSheet.Name = Range("MyName")

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
D

Dominique

Awesome! Thank you. If I could trouble you for another question:

So, I've written the macro:

Sub MyMacro()
ActiveSheet.Name = Range("MyRange")
End Sub

and then dropped the following into the worksheet code:

Private Sub Worksheet_Activate()
Run "MyMacro"
End Sub

Do you know how I can place the VBscript directly into the worksheet code
without having to create a macro? I ask because I'll have about 30 macros to
create otherwise.

Many, many thanks.
Dominique
 
R

Richard Buttrey

The macro does not have to be triggered by a Worksheet Activate event.
That was only a suggestion

If the named range containing the name you want for the sheet is
always in the same position, (say A1 in the example below), the
following macro could be placed in a module procedure. e.g.


Sub NameSheets()
Dim x As Integer
For x = 1 To ActiveWorkbook.Sheets.Count
If Worksheets(x).Range("A1") <> "" Then
Worksheets(x).Name = Worksheets(x).Range("A1")
End If
Next x
End Sub

It loops through every sheet and names the sheet accoding to the
contents of A1. You might need to modify this if other sheets that you
don't want to rename have something in A1. The trick is to have
something consistent about the sheets in question and unique to them.
Trivially you could mark each sheet to be renamed with say an 'x' in
Z1, then the test becomes

If Worksheets(x).Range("Z1") = "x" Then

HTH.


Awesome! Thank you. If I could trouble you for another question:

So, I've written the macro:

Sub MyMacro()
ActiveSheet.Name = Range("MyRange")
End Sub

and then dropped the following into the worksheet code:

Private Sub Worksheet_Activate()
Run "MyMacro"
End Sub

Do you know how I can place the VBscript directly into the worksheet code
without having to create a macro? I ask because I'll have about 30 macros to
create otherwise.

Many, many thanks.
Dominique
Click to expand...

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
D

Dominique

Wow! The perfect solution to the problem. Thank you so very much!! You're
absolutely right about having it in the workbook module...you're my new
favorite person of the day, btw!

Regards, Dominique
 
Top