Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to change the sheet name by the cell value?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to change the sheet name by the cell value?
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 On Sat, 7 Jun 2008 08:29:01 -0700, Kim wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to change the sheet name by the cell value?
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) -- Cheers, Shane Devenshire Microsoft Excel MVP "Kim" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change excel sheet from R1C1 for cells to A1 for cell? | Excel Worksheet Functions | |||
Want cell ref. to change after sort in other sheet | Excel Discussion (Misc queries) | |||
Change the work sheet name in a formula by using cell reference | Excel Worksheet Functions | |||
Change sheet name on linked cell by dropdown box | Excel Worksheet Functions | |||
Change workbook sheet reference using cell A1 to change a vairable | Excel Worksheet Functions |