Excel Forum

Code: Select all

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Dim WS As Worksheet
  Dim i As Integer
    Set WS = ActiveSheet
        If Target.Address(0, 0) = "R5" Then
            WS.Name = WS.Range("R5").Value & "00" & i + 1
    
  End If
    
End Sub

Code: Select all

Sub Test0()
    Dim sh As Worksheet, i As Integer
    
    i = 1
    For Each sh In Worksheets
        If sh.Index > 1 Then
            sh.Name = sh.Name & "_" & VBA.Format(i, "00")
            i = i + 1
        End If
    Next sh
End Sub

Code: Select all

Sub Test0()
    Dim Sh As Worksheet, i As Integer
    Dim strPre As String
    
    strPre = Worksheets(2).Range("r5").Value
    For Each Sh In Worksheets
        If Sh.Index > 1 Then
            Sh.Name = strPre & VBA.Mid(Sh.Name, 5)
        End If
    Next Sh
End Sub