rename sheets and increment continuously for character @

[Hasson]

Hello
I search for macro to rename any sheets name contain "@" and increment numbers
so the sheets names could be
file@1-1-2023
MNTYVB@1-2-2023
result should be
MT1
MT2
and if I add new sheets like this
asetubn67@1-1-2023
xadfg@1-2-2023
then will increment like this
MT3
MT4
so the final result will be
MT1
MT2
MT3
MT4
and so on when add new sheets contain @ should rename MT and increment numbers.

 

[DanteAmor]

Thanks for posting on the forum.


Please try the following macro:

Sub Rename_Sheets()
  Dim sh As Worksheet
  Dim sName As String
  Dim nMax As Long, num As Long
  
  sName = "MT"
  
  For Each sh In Sheets
    If Left(sh.Name, Len(sName)) = sName Then
      num = Mid(sh.Name, 3)
      If num > nMax Then
        nMax = num
      End If
    End If
  Next
  
  nMax = nMax + 1
  
  For Each sh In Sheets
    If InStr(1, sh.Name, "@") > 0 Then
      sh.Name = sName & nMax
      nMax = nMax + 1
    End If
  Next
End Sub

Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor

 

[Hasson]

well done !
always your assistance is great !
many thanks buddy !