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

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

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
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

Macro to name worksheet tabs using a cell within the worksheet
 

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
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

Macro to name worksheet tabs using a cell within the worksheet
 

Happy to assist.

Thanks for the feedback.


Gord


On Tue, 18 Jul 2006 13:10:02 -0700, Jennifer
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
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

What if I just want ot perform the procedure for a range of sheets, lets say sheet1 to sheet24. How do I set the loop just for that range. Thnx.