Worksheet name equal cell value

[Paul]

I am looking for a way to have worksheets automatically
renamed to the value entered in cell A1, either
individually or all together (for all worksheets in the
workbook). Need this in a hurry if anyone has a
solution. Thanks

 

[rbrychckn]

Don't know how to do Cell to Sheet, but from Sheet to cell is a
follows:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3

-To

 

[John Wilson]

Paul,

Probably a more concise way out there, but this should do it:

Sub TestMe()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate
ActiveSheet.Name = Range("A1").Value
Next ws
End Sub

John

 

[Andy Brown]

Now all you need is an error handler for invalid characters,

: , \ / "history"

, etc.

Rgds,
Andy

 

[gr8guy]

Hi Paul,

If you mean to rename the active sheet with the value located in cell A1 of
each worksheet that is active at that time, you can enter either one of the
following code in the ThisWorkbook Code:

1] Right-click any sheet
2] Select "View Code"
3] In the "Project Explorer" window on left, select and double-click
"ThisWorkbook". The "ThisWorkbook" code pane will open.
4] Copy the following code.

a) Note that this code will update the sheet name only if you activate
(select it) the next time. i.e if you enter a value in A1 on Sheet1, then
select Sheet2, then again select Sheet1 again, automatically Sheet1 would be
renamed as the value given in Sheet1. I suggest you go for the latter one
where you donot have to click any other sheets to update the active sheet.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next
ActiveSheet.Name = Range("A1")
End Sub


b) Note this would work automatically on the active sheet selected, once you
enter any value in cell A1, the sheet name will change at that instance.

This is fast!!!!!

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
On Error Resume Next
ActiveSheet.Name = Range("A1")
End Sub

Lastly, I need to inform you that both codes will give errors,
if you enter 'similar values' or 'Invalid characters',
so the error handler "On Error Resume Next" is used.

Inother words, you cannot have 2 worksheets with the same names!


Rgds,

Eijaz