how to change the sheet name by the cell value?

[Kim]

Hi,

I've created a macro to insert a new sheet but I have a list of name in a
sheet. Can I code vba to change the new sheet name as my list.
Suppose in cell A1, its value is Cat. (In sheet1).
When I insert a new sheet. I want to change the sheet name as Cat
automatically.

How can I do that?
Thank you.

 

[Gord Dibben]

Insert one sheet or multiples based on a list?

Play with these........adjust to suit.

Sub Add_Sheets()
Dim rCell As Range
For Each rCell In Sheets("Sheet1").Range("A1:A5")
With Worksheets.Add(After:=Worksheets(Worksheets.Count))
.Name = rCell.Value
End With
Next rCell
End Sub


Sub Add_One_Sheet()
Dim rCell As Range
Sheets("Sheet1").Select
Set rCell = Application.InputBox(prompt:= _
"Select A Cell", Type:=8)
With Worksheets.Add(After:=Worksheets(Worksheets.Count))
.Name = rCell.Value
End With
End Sub


Gord Dibben MS Excel MVP

 

[ShaneDevenshire]

Hi Kim,

The following macro will automatically change the sheet name to match the
contents of cell A1 provide there are not illegal characters in cell a1.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
ActiveSheet.Name = [A1]
End If
End Sub

The problem is that you want this to work for all new sheets? If you want
it to work for all sheet in a workbook its easy - just modify the code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$A$1" Then
ActiveSheet.Name = [A1]
End If
End Sub

and put this in the thisWorkbook object in the VBE.

To make the first one really automatic - so you don't need to add the code
each time you add a new worksheet, you will need to create a default sheet.

1. Open a blank workbook
2. Delete all the sheets except sheet1
3. Open the VBA editor and paste the first code above into the Sheet1
object - double-click Sheet1 in the Project Explorer.
4. Close the editor
5. In Excel choose File, Save As,
6. Change the filename to Sheet
7. Change the Files of Type to Template (*.xls)
8. Save the file in the \XLStart folder (this folder is usually in Program
Files\Microsoft Office\Office ##\ (where ## depends on your version of
Excel)