Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
|
|||
|
|||
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.
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get ONLY new info from 1 Worksheet to another automatical | Excel Worksheet Functions | |||
Adding a row to worksheet does not update cell references in another. | Excel Worksheet Functions | |||
macro help | Excel Discussion (Misc queries) | |||
Macro to search for and display data in another worksheet | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions |