Is it possible to rename the worksheet with vba code?

[dener123]

How can I write the scenario for the following case?

If sheet name "Jan" exists, rename this sheet as "Feb".

 

[dener123]

So how many sheets are in this workbook exactly?
Do you want to rename ALL the prior worksheets?

There are multiple sheets/tabs in this workbook.

I'm trying to make a code that creates new sheet/tab and rename itself as the month after the prior one. (ex. If there is tab named Jan already, the newly created tab will rename itself as Feb... If there is tab named Feb already, the newly created tab will rename itself as Mar... and so on all the way up to Dec (december) and that's it.

 

[dener123]

From Post #1: "If sheet name "Jan" exists, rename this sheet as "Feb"."
Now you say that it could be any month of the year.

At first I thought having that scenario alone, I can just change the name of sheet/tab's name and repeat the code all the way til december... :'(

 

[rlv01]

Without knowing the larger picture, this is a bit of a guess.

Sub SaveActiveSheet()
    
    Dim X As Variant, Months As Variant
    Dim I As Long
    Dim M As String, ShList As String
    Dim NewName As Boolean
    
    Months = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
    ShList = VBA.Join(Months, "!")
    
    For I = 0 To UBound(Months)
        NewName = False
        X = vbNullString
        M = Months(I)
        
        On Error Resume Next
        X = ActiveWorkbook.Worksheets(M).Name
        On Error GoTo 0
        
        If X = vbNullString And InStr(ShList, ActiveSheet.Name & "!") = 0 Then
            Select Case ActiveSheet.Name
                Case "Main", "Summary" '<- sheets to be ignored
                Case Else
                    ActiveSheet.Name = M
                    NewName = True
                    Exit For
            End Select
        End If
    Next I
    If Not NewName Then
        MsgBox "All Months are taken!", vbCritical
    End If
End Sub

 

[dener123]

Without knowing the larger picture, this is a bit of a guess.

Sub SaveActiveSheet()
   
    Dim X As Variant, Months As Variant
    Dim I As Long
    Dim M As String, ShList As String
    Dim NewName As Boolean
   
    Months = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
    ShList = VBA.Join(Months, "!")
   
    For I = 0 To UBound(Months)
        NewName = False
        X = vbNullString
        M = Months(I)
       
        On Error Resume Next
        X = ActiveWorkbook.Worksheets(M).Name
        On Error GoTo 0
       
        If X = vbNullString And InStr(ShList, ActiveSheet.Name & "!") = 0 Then
            Select Case ActiveSheet.Name
                Case "Main", "Summary" '<- sheets to be ignored
                Case Else
                    ActiveSheet.Name = M
                    NewName = True
                    Exit For
            End Select
        End If
    Next I
    If Not NewName Then
        MsgBox "All Months are taken!", vbCritical
    End If
End Sub

Thank you so much!!!!
This is exactly what I was trying to do.

Thank you!!

 

[jolivanes]

Macro assumes that you have at least a Sheet named "Jan" (without double quotation marks)
You can have as many as 12 Sheets named for each month. If Dec is present, it just will not add a sheet.
Check your Custom Lists to ensure that the short month list is the third in the list. Change if required.

Sub Add_Next_Month()
Dim ListArray
Dim i As Long
ListArray = Application.GetCustomListContents(3)
    For i = LBound(ListArray, 1) To UBound(ListArray, 1)
        On Error Resume Next
            If IsEmpty(ActiveWorkbook.Sheets(ListArray(i)).Name) Then ThisWorkbook.Sheets.Add(, Sheets(Sheets.Count)).Name = ListArray(i): Exit For
        On Error GoTo 0
    Next i
End Sub

So If you have Jan, Feb, Mar as Sheet names, macro will add Apr.
If you have Jan, Feb, Mar, Apr, May, Jun, Jul as worksheets, it will add Aug.