Automatically name worksheets

S

shapiro

I have over worksheets that I would like to name by referencing to a cell. I
have seen some postings here which suggests a macro formula but I keep
getting error. Does anyone have any suggestions?
 
M

Max

shapiro said:
I have over ?? worksheets that I would like to name by referencing to a cell. I
have seen some postings here which suggests a macro formula but I keep
getting error. Does anyone have any suggestions?
Click to expand...

I've used this sub kindly given by Neil for years without problem <g>:

Sub RenameWS()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
ws.Name = Range("B2").Value
Next
End Sub
 
M

Max

Just to add that I've also diligently ensured the following rules for
sheetnames before running the sub ..

1. Name in B2 does not exceed 31 characters
2. Name in B2 does not contain any of the following characters:
: \ / ? * [ or ]
3. that there's a name in B2 (ie B2 is not blank)
 
G

Gord Dibben

How about whenever you change the value of a particular cell?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
Me.Name = Range("A1").Text
CleanUp:
Application.EnableEvents = True
End Sub

This is sheet event code.

Right-click on the sheet tab and "View Code"

Copy/paste the above into that sheet module.

Change the value of A1 and sheet name will follow.


Gord Dibben MS Excel MVP
 
M

Max

Hi Gord, just a little side-track here ..
.. This is sheet event code.
Right-click on the sheet tab and "View Code"
Copy/paste the above into that sheet module
Click to expand...

Is there code which can auto-apply the sheet event code
into every sheet in the book ?
 
G

Gord Dibben

Max

I thought about later last night and realized I had posted code for just one
worksheet and OP wanted all worksheets.

Was going to re-post this morning so here goes.

Enter this in the Thisworkbook module not in a sheet module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'enter event code here
End Sub

Will cover all sheets.


Gord

Hi Gord, just a little side-track here ..


Is there code which can auto-apply the sheet event code
into every sheet in the book ?
Click to expand...

Gord Dibben MS Excel MVP
 
M

Max

Gord, tried as suggested, with the sub below placed in the ThisWorkbook
module. Testing on any sheet with an entry in A1 resulted in a compile error:
Method or data member not found. The ".Range" in Me.Range("A1") was
highlighted. Anything I can do to get this going ? Thanks.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
Me.Name = Range("A1").Text
CleanUp:
Application.EnableEvents = True
End Sub
 
D

Dave Peterson

Me would refer to the workbook owning the code (since you're in the ThisWorkbook
module).

Try:
sh.range("a1")

(since that sh is getting passed to the subroutine.)

Don't forget to change this line, too:
Me.Name = Range("A1").Text
to
sh.Name = sh.Range("A1").Text



And if you were using the equivalent code within a worksheet module, you would
have been ok.
 
G

Gord Dibben

Thanks Dave.

All this stuff I forget to alter<g>

Didn't want to use worksheet module because all sheets have to be covered by the
event.


Gord
 
S

shapiro

Thanks Max.. the tip realy worked!

Max said:
Just to add that I've also diligently ensured the following rules for
sheetnames before running the sub ..

1. Name in B2 does not exceed 31 characters
2. Name in B2 does not contain any of the following characters:
: \ / ? * [ or ]
3. that there's a name in B2 (ie B2 is not blank)

Max said:
I've used this sub kindly given by Neil for years without problem <g>:

Sub RenameWS()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
ws.Name = Range("B2").Value
Next
End Sub
Click to expand...
Click to expand...
 
M

Max

.. The formula is perfect!
That's called "code", not "formula" <g>

But guess "formula/s" could be called:
user friendly, compressed code
 
Top