Sorry Barb for wrong post.
This was intended to Jim's post, Message Id:
22B8A7A7-0541-4446-8A20-(E-Mail Removed)>
Keiji
keiji kounoike 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
> Ignore:
> 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 Ignore:
> 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
Similar Threads
