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

 

[ianccy]

Now if I have created 200 sheets with names with the method provided, now if i want to change all the sheets name with a new list of names without deleting this sheets , just renaming them by using data from Shee1 A column, how to do this?

 

[skorpionkz]

Just run code again with updated list

 

[ianccy]

try this. It check number of rows in Column Q and if it >= 100 then copy Sheet Name to Sheet1

Option Explicit

Sub wsNameList()

Application.ScreenUpdating = False

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
        If ws.Cells(10000, 17).End(xlUp).Row >= 100 Then
        wsList.Cells(lSheetNameRow, 1) = ws.Name
        lSheetNameRow = lSheetNameRow + 1
        End If
    End If
Next ws
   
Application.ScreenUpdating = True

End Sub

Hi skorpionkz, what code to delete the Sheets with less than 100 rows instead of just list the names in sheet1?

 

[skorpionkz]

you need just 1 little adjustment:

Option Explicit

Sub wsNameList()

Application.ScreenUpdating = False

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
        If ws.Cells(10000, 17).End(xlUp).Row >= 100 Then
        wsList.Cells(lSheetNameRow, 1) = ws.Name
        lSheetNameRow = lSheetNameRow + 1
        Else
        ws.Delete
        End If
    End If
Next ws
   
Application.ScreenUpdating = True

End Sub

 

[ianccy]

Thanks. Is it possible to not show up the Delete confirmation dialog box?

 

[skorpionkz]

sure.

add line:

Application.ScreenUpdating = False
Application.DisplayAlerts = False

and

Application.ScreenUpdating = True
Application.DisplayAlerts = True