Macro to name worksheet tabs using a cell within the worksheet?

For example, if I have 50 worksheets in a workbook and in each one, cell A1
names the item, is there a macro I can create to automatically name the tabs
with the name in A1?

TIA for any help

Jennifer

If each sheet's A1 entry is unique and no invalid charaters, this will do the
job.

Sub wsname()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Name = ws.Cells(1, 1).Value
Next ws
End Sub

If a chance of any duplicates or invalid characters try this error-trapped
version from Ron de Bruin

Sub Sheetname_cell()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
On Error Resume Next
sh.Name = sh.Range("A1").Value
'next lines cover duplicate names
If Err.Number > 0 Then
MsgBox "Change the name of : " & sh.Name & " manually"
Err.Clear
End If
On Error GoTo 0
Next
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Tue, 18 Jul 2006 12:10:02 -0700, Jennifer
<[email protected]> wrote:

>For example, if I have 50 worksheets in a workbook and in each one, cell A1
>names the item, is there a macro I can create to automatically name the tabs
>with the name in A1?
>
>TIA for any help

Thank you very much! The second one worked for me. You've been a great help!

"Gord Dibben" wrote:

> Jennifer
>
> If each sheet's A1 entry is unique and no invalid charaters, this will do the
> job.
>
> Sub wsname()
> Dim ws As Worksheet
> For Each ws In ActiveWorkbook.Worksheets
> ws.Name = ws.Cells(1, 1).Value
> Next ws
> End Sub
>
> If a chance of any duplicates or invalid characters try this error-trapped
> version from Ron de Bruin
>
> Sub Sheetname_cell()
> Dim sh As Worksheet
> Application.ScreenUpdating = False
> For Each sh In ThisWorkbook.Worksheets
> On Error Resume Next
> sh.Name = sh.Range("A1").Value
> 'next lines cover duplicate names
> If Err.Number > 0 Then
> MsgBox "Change the name of : " & sh.Name & " manually"
> Err.Clear
> End If
> On Error GoTo 0
> Next
> Application.ScreenUpdating = True
> End Sub
>
>
> Gord Dibben MS Excel MVP
>
> On Tue, 18 Jul 2006 12:10:02 -0700, Jennifer
> <[email protected]> wrote:
>
> >For example, if I have 50 worksheets in a workbook and in each one, cell A1
> >names the item, is there a macro I can create to automatically name the tabs
> >with the name in A1?
> >
> >TIA for any help
>
>

Happy to assist.

Thanks for the feedback.


Gord


On Tue, 18 Jul 2006 13:10:02 -0700, Jennifer
<[email protected]> wrote:

>Thank you very much! The second one worked for me. You've been a great help!
>
>"Gord Dibben" wrote:
>
>> Jennifer
>>
>> If each sheet's A1 entry is unique and no invalid charaters, this will do the
>> job.
>>
>> Sub wsname()
>> Dim ws As Worksheet
>> For Each ws In ActiveWorkbook.Worksheets
>> ws.Name = ws.Cells(1, 1).Value
>> Next ws
>> End Sub
>>
>> If a chance of any duplicates or invalid characters try this error-trapped
>> version from Ron de Bruin
>>
>> Sub Sheetname_cell()
>> Dim sh As Worksheet
>> Application.ScreenUpdating = False
>> For Each sh In ThisWorkbook.Worksheets
>> On Error Resume Next
>> sh.Name = sh.Range("A1").Value
>> 'next lines cover duplicate names
>> If Err.Number > 0 Then
>> MsgBox "Change the name of : " & sh.Name & " manually"
>> Err.Clear
>> End If
>> On Error GoTo 0
>> Next
>> Application.ScreenUpdating = True
>> End Sub
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Tue, 18 Jul 2006 12:10:02 -0700, Jennifer
>> <[email protected]> wrote:
>>
>> >For example, if I have 50 worksheets in a workbook and in each one, cell A1
>> >names the item, is there a macro I can create to automatically name the tabs
>> >with the name in A1?
>> >
>> >TIA for any help
>>
>>

Gord Dibben MS Excel MVP