RENAME TABS: change "prefixed" sheet name to new "prefix"

[itr674]

I need some some VBA code to chnage worksheet names that are prefixed with a, b, c, d, e, ect. to a diffeernt letter prefix via a MsgBox.

The sheet names are typically a00, a01, a002, b00, b01, b02 etc. and I just need to change the left most character to a new letter.

I have changed the prefix on certain sheets, so that frees up their previous prefix letter for use on other sheets.

Is this doable?

 

[James006]

Hi,
To loop sheets, you can test following

Sub Loop_Sheets()
Dim ws As Worksheet
' Example Replace S by T ...
    For Each ws In ThisWorkbook.Worksheets
        ws.Name = Replace(ws.Name, Left(ws.Name, 1), "T")
    Next ws
End Sub

 

[HaHoBe]

Hi itr674,

what about

Public Sub MrE_1232793_1704D08()
' https://www.mrexcel.com/board/threads/rename-tabs-change-prefixed-sheet-name-to-new-prefix.1232793/

Dim ws As Worksheet
Dim varRetOld As Variant
Dim varRetNew As Variant

varRetOld = LCase(InputBox("Replace which character?", "Only one character"))
If varRetOld = "" Then Exit Sub
If Len(varRetOld) > 1 Then varRetOld = Left(varRetOld, 1)

varRetNew = LCase(InputBox("New character", "Only one character"))
If varRetNew = "" Then Exit Sub
If Len(varRetNew) > 1 Then varRetNew = Left(varRetNew, 1)
Select Case Asc(varRetNew)
  Case 97 To 122
  Case Else
    MsgBox varRetNew & " is not allowed here", vbInformation, "Ending here"
    Exit Sub
End Select

If Not Evaluate("ISREF('" & varRetNew & "00'!A1)") Then
  For Each ws In Worksheets
    ws.Name = Replace(ws.Name, varRetOld, varRetNew)
  Next ws
Else
  MsgBox varRetNew & " is in use, choose a different character.", vbInformation, "Start procedure again"
End If

End Sub

Code will work with only one character and always lower case.

Ciao,
Holger

 

[itr674]

HaHoBe and James006, thank you.

HaHoBe
I built a test book and ran the code and it worked great, but I only need to change the 1st character of the sheet name. The code changes the letter no matter where it is in the sheet name.
The MsgBox worked great also.

James006
I can probably take your code and place it inside HaHoBe's and that will change only the 1 character, but headed to bed for the night.

 

[HaHoBe]

Hi itr674,

small alteration to the code:

Public Sub MrE_1232793_1704D08_Update()
' https://www.mrexcel.com/board/threads/rename-tabs-change-prefixed-sheet-name-to-new-prefix.1232793/
' Update: only work with the first character from the sheetname

Dim ws As Worksheet
Dim varRetOld As Variant
Dim varRetNew As Variant

varRetOld = LCase(InputBox("Replace which character?", "Only one character"))
If varRetOld = "" Then Exit Sub
If Len(varRetOld) > 1 Then varRetOld = Left(varRetOld, 1)

varRetNew = LCase(InputBox("New character", "Only one character"))
If varRetNew = "" Then Exit Sub
If Len(varRetNew) > 1 Then varRetNew = Left(varRetNew, 1)
Select Case Asc(varRetNew)
  Case 97 To 122
  Case Else
    MsgBox varRetNew & " is not allowed here", vbInformation, "Ending here"
    Exit Sub
End Select

If Not Evaluate("ISREF('" & varRetNew & "00'!A1)") Then
  For Each ws In Worksheets
    If Left(ws.Name, 1) = varRetOld Then ws.Name = Replace(Left(ws.Name, 1), varRetOld, varRetNew) & Mid(ws.Name, 2)
  Next ws
Else
  MsgBox varRetNew & " is in use, choose a different character.", vbInformation, "Start procedure again"
End If

End Sub

Ciao,
Holger

 

[itr674]

test

 

[itr674]

HaHoBe, perfect, thank you!