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?

"shapiro" wrote:
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?

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Max" wrote:
"shapiro" wrote:
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?

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

On Mon, 28 Aug 2006 18:16:02 -0700, shapiro
wrote:

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?

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

Is there code which can auto-apply the sheet event code
into every sheet in the book ?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

On Tue, 29 Aug 2006 02:05:02 -0700, Max wrote:

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

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

Gord Dibben MS Excel MVP

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gord Dibben" wrote:
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

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.


Max wrote:

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gord Dibben" wrote:
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

--

Dave Peterson

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

On Tue, 29 Aug 2006 11:28:13 -0500, Dave Peterson
wrote:

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.


Max wrote:

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gord Dibben" wrote:
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

Thanks Max.. the tip realy worked!

"Max" wrote:

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Max" wrote:
"shapiro" wrote:
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?

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

I do appreciate everyones help. The formula is perfect!

"Gord Dibben" wrote:

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

On Mon, 28 Aug 2006 18:16:02 -0700, shapiro
wrote:

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?

"Gord Dibben" wrote:
Thanks Dave

Make that a double from me <g!
Thanks for the corrections & explanation, Dave
Its working fine.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Shapiro, you're welcome!
Thanks for calling back ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"shapiro" wrote:
Thanks Max.. the tip really worked!

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

But guess "formula/s" could be called:
user friendly, compressed code
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---