faster way to changing Sheet tab name

[ianccy]

I want to change the Sheet name according to a column of data pasted to some cells,

I will paste the data to Sheet1 A1:A200, Sheet2 will be named "A1" , Sheet3 will be named "A2", and the rest of the sheets will be added and named,

How to do this,

Thanks for any help

 

[skorpionkz]

try this

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit

Sub wsNameChange()

Dim wsList As Worksheet, ws As Worksheet
Dim lSheetNameRow As Long

Set wsList = Sheet1

lSheetNameRow = 1

For Each ws In Sheets
    If Not ws.Name = wsList.Name Then
    ws.Name = wsList.Cells(lSheetNameRow, 1)
     lSheetNameRow = lSheetNameRow + 1
    End If
Next ws
   
Do Until wsList.Cells(lSheetNameRow, 1) = Empty
    Sheets.Add.Name = wsList.Cells(lSheetNameRow, 1)
     lSheetNameRow = lSheetNameRow + 1
Loop

End Sub

 

[skorpionkz]

Option Explicit

Sub wsNameChange()

Application.ScreenUpdating = False

Dim wsList As Worksheet, ws As Worksheet
Dim lSheetNameRow As Long, lSheetCount As Long

Set wsList = Sheet1

lSheetNameRow = 1

For Each ws In Sheets
    If Not ws.Name = wsList.Name Then
    ws.Name = wsList.Cells(lSheetNameRow, 1)
     lSheetNameRow = lSheetNameRow + 1
    End If
Next ws
   
Do Until wsList.Cells(lSheetNameRow, 1) = Empty
    lSheetCount = ActiveWorkbook.Worksheets.Count
    Sheets.Add(after:=Sheets(lSheetCount)).Name = wsList.Cells(lSheetNameRow, 1)
    lSheetNameRow = lSheetNameRow + 1
Loop

Application.ScreenUpdating = True

End Sub

Little change. New sheets are added after all existing sheets and not before.

 

[ianccy]

It works, but it add the sheet before Sheet1, how to insert the next sheet after the last one?

OK, thanks for the fast response @@

 

[ianccy]

Hi skorpionkz,

Is it possible to keep Sheet2 and Sheet3, without removing them? Thanks alot

 

[skorpionkz]

code does not remove anything.
Do you mean without changing names of those sheets?

 

[ianccy]

yes, i still want sheet2 and sheet 3 to exist, the new sheets will be placed after Sheet2 and before sheet3

 

[skorpionkz]

try this code:

Option Explicit

Sub wsNameChange()

Application.ScreenUpdating = False

Dim wsList As Worksheet, ws As Worksheet
Dim lSheetNameRow As Long, lSheetCount As Long

Set wsList = Sheet1

lSheetNameRow = 1

For Each ws In Sheets
    If Not ws.Name = wsList.Name Then
        If Not LCase(ws.Name) Like "sheet2" And Not LCase(ws.Name) Like "sheet3" Then
            ws.Name = wsList.Cells(lSheetNameRow, 1)
            lSheetNameRow = lSheetNameRow + 1
        End If
    End If
Next ws
   
Do Until wsList.Cells(lSheetNameRow, 1) = Empty
    lSheetCount = ActiveWorkbook.Worksheets.Count
    Sheets.Add(After:=Sheets(lSheetCount)).Name = wsList.Cells(lSheetNameRow, 1)
    lSheetNameRow = lSheetNameRow + 1
Loop

lSheetCount = ActiveWorkbook.Worksheets.Count
ActiveWorkbook.Sheets("Sheet3").Move After:=Sheets(lSheetCount)
Application.ScreenUpdating = True

End Sub

 

[ianccy]

Works, thanks

and if i have codes in Sheet3， and I would want the same codes to copied to all the new sheets, how to do?

like if sheet3 named “Room 03” have the following code lines in it, “Room.03 ......” & "Room_03 ...........", the new sheet named "Room 04" will have the codes “Room.04 ......” & "Room_04 ...........", etc, can this be done?

sometime my worksheets name may be in "10011 Room"

 

[skorpionkz]

I don't understand this part

like if sheet3 named “Room 03” have the following code lines in it, “Room.03 ......” & "Room_03 ...........", the new sheet named "Room 04" will have the codes “Room.04 ......” & "Room_04 ...........", etc, can this be done?