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".

 

[Cubist]

Something like this.

Sub Rename()
    Dim ws As Worksheet
    Dim sheetExists As Boolean
    sheetExists = False
   
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "Jan" Then
            sheetExists = True
            ws.Name = "Feb"
            Exit For
        End If
    Next ws
   
    If Not sheetExists Then
        MsgBox "Sheet 'Jan' does not exist."
    Else
        MsgBox "Sheet 'Jan' has been renamed to 'Feb'."
    End If
End Sub

 

[rlv01]

There a few different ways. Here is one.

    Dim X As Variant
    
    On Error Resume Next
    X = ActiveWorkbook.Worksheets("Jan").Name
    On Error GoTo 0
    
    If IsEmpty(X) Then
        ActiveSheet.Name = "Jan"
    Else
        ActiveSheet.Name = "Feb"
    End If

 

[dmt32]

Another idea maybe if you want to change tab name each month

Sub Rename()
    Dim LastMonth As String, ThisMonth As String
    
    LastMonth = MonthName(Month(Date) - 1, True)
    ThisMonth = MonthName(Month(Date), True)
    
    If Evaluate("ISREF('" & LastMonth & "'!A1)") Then ThisWorkbook.Worksheets(LastMonth).Name = ThisMonth
   
End Sub

Dave

 

[jolivanes]

The proverbial "Roads to Rome". Many of them.

Sub Change_Sheet_Name()
Dim sh As Worksheet
    For Each sh In ThisWorkbook.Worksheets
            If [ISREF(Jan!A1)] Then Sheets("Jan").Name = "Feb": Exit For
    Next sh
End Sub

 

[My Aswer Is This]

Try this:

Private Sub CommandButton1_Click()
Dim i As Long
For i = 1 To Sheets.Count
Select Case Sheets(i).Name
Case "Jan": Sheets(i).Name = "Feb"
End Select
Next
End Sub

 

[dener123]

Hey guys,
I really appreciate all the replies. I think I wasn't clear enough with the scenario.

Let's say I have multiple worksheets named Jan, Feb, Mar, Apr, and May.
I need a VBA code that creates a new worksheet and rename itself as the month after if the prior month sheets exist.

For example;
If worksheet named Jan already exist, rename this newly created sheet as Feb.
If worksheet named Feb already exist, rename this newly created sheet as Mar.
and so on until Dec.

 

[dener123]

There a few different ways. Here is one.

    Dim X As Variant
   
    On Error Resume Next
    X = ActiveWorkbook.Worksheets("Jan").Name
    On Error GoTo 0
   
    If IsEmpty(X) Then
        ActiveSheet.Name = "Jan"
    Else
        ActiveSheet.Name = "Feb"
    End If

Hi,
This code works in the scenario I've wanted!
But could you make this longer?

What I meant by that is;
I need this all the way to Dec.

For example;
If worksheet named Jan already exist, rename this newly created sheet as Feb.
If worksheet named Feb already exist, rename this newly created sheet as Mar.
and so on all the way to Dec.

 

[jolivanes]

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.

 

[Joe4]

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