Brilliant! You anticipated my every need.

Thanks Heaps.

Cheers
--
Jim


"keiji kounoike" <"kounoike AT mbh.nifty." wrote:

Try this one.

Sub UpdateTabName()
Dim Ws As Worksheet
Dim n

With ActiveWorkbook
For Each Ws In Worksheets
On Error GoTo ex:
Ws.Select
Ws.Name = Ws.Range("A1").Text
Igno
Next Ws

End With
Exit Sub
ex:
n = Application.InputBox("Wrong Sheet'S Name!!", _
Default:=Ws.Range("A1").Text, Type:=2)
If VarType(n) = vbBoolean Then
Resume Igno
End If
Ws.Range("A1") = n
Resume
End Sub

Keiji

Barb Reinhardt wrote:
You could try something like this

For Each Ws In .Worksheets
On Error Resume next
Ws.Name = Ws.Range("A1") €˜ change to the cell address to be used
If Err < 0 then
WS.Select
on error resume next
WS.Name = InputBox("Enter Worksheet Name",WSName)
if err < 0 then Msgbox ("Unable to set worksheet name.")
end if
on error goto 0
Next Ws

"Jim G" wrote:

I have the following code that changes the sheet name based on the entry in
cell A1. This allows the user to create multiple sheet copies of a template
then updateing the data in each without needing to update sheet names as they
go.

Sub UpdateTabName()
Dim Ws As Worksheet
€˜ the macro will rename all sheets in the active workbook to the contents of
A1

With ActiveWorkbook

For Each Ws In Worksheets
Ws.Name = Ws.Range("A1") €˜ change to the cell address to be used
Next Ws

End With

End Sub

I would like to insert a error trap and message for when the target cell is
blank or has illegal characters. Ideally, I would like to provide a means
for the user to be prompted for a new name for that sheet. If that is not
possible or practical, insert a temporary name and alert the user to change
it.

Any suggestions?

--
Jim