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]

for example, sheet 3 named “103.Room"， it has some codes like this,


Sub Refreshw_103_Room()
'---------------------------
'Macros Room_maintenance
' 103.Room - Weekly
'---------------------------

MyAdress = "A1"
KolDN = 5169
MyFixedFloat = 1
Period = "w"
.
.
.
.
LockChanged = "103.Room"
MyRange = "A1:F180"
MyNaprav = 1
.
.
.
.
Public Sub RefreshSheet()

Call Refreshw_103_Room

End Sub


when add the new sheet "104.Room" after Sheet3, sheet4 should also include the codes


Sub Refreshw_104_Room()
'---------------------------
'Macros Room_maintenance
' 104.Room - Weekly
'---------------------------

MyAdress = "A1"
KolDN = 5169
MyFixedFloat = 1
Period = "w"
.
.
.
.
LockChanged = "104.Room"
MyRange = "A1:F180"
MyNaprav = 1
.
.
.
.
Public Sub RefreshSheet()

Call Refreshw_104_Room

End Sub



where the "103" number in the codes in sheet3 is now changed to "104" in Sheet4 (as relative to the Sheet4 name "104")，

I have many sheets else i have to edit them for a long time, anyway I can quickly change it?

 

[skorpionkz]

I am not sure can you copy the Code to the other sheets. You can make the copy of Sheet3 and then change the sheet name of this copy but it will have the exactly same code. you can of course make it work, but you need to change code in the sheet so it will use variable which base on the sheet name (and do changes in it like replace "." with "_") instead of manual declare this variable.

Let me know if this make sens.
If not then better create new thread as this request is not related to what was requested in this thread. So the solution for the new request will not be so easy searchable.

 

[ianccy]

I will make a new thread, thank you for all the help, you are awesome.

 

[ianccy]

Is there a reverse way do this？ that is to extract Sheets names from my workbook and list them in Sheet 1 A1:A250

 

[skorpionkz]

try this:

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

End Sub

 

[ianccy]

Thank you again, skorpionkz

 

[ianccy]

Another question, if i don't want the name of sheets with data row less than 100 rows to be added to the name list in Sheet1, what is the codes to do this? thank you

 

[skorpionkz]

in which column you would check the row number?

 

[ianccy]

anywhere beyond column P

 

[skorpionkz]

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