How to create a new sheet with today’s date as its name using VBA?

Creating a new sheet with today’s date as its name using VBA in Excel is a straightforward process. You can achieve this by writing a simple VBA macro. Here is a step-by-step guide along with the code:

Sub CreateSheetWithTodaysDate()
    Dim newSheet As Worksheet
    Dim sheetName As String
    
    ' Get today's date and format it as "YYYY-MM-DD"
    sheetName = Format(Date, "YYYY-MM-DD")

    ' Check if the sheet already exists to avoid errors
    On Error Resume Next
    Set newSheet = ThisWorkbook.Worksheets(sheetName)
    On Error GoTo 0
    
    If newSheet Is Nothing Then
        ' Create a new sheet with today's date as the name
        Set newSheet = ThisWorkbook.Worksheets.Add
        newSheet.Name = sheetName
    Else
        MsgBox "A sheet with today's date already exists."
    End If
End Sub

Open Excel and Press `ALT + F11:` This will open the Visual Basic for Applications (VBA) editor.
Insert a New Module:

Right-click on any existing VBA project in the `Project Explorer` pane.
Select `Insert > Module` to create a new module where you can write your code.

Write the VBA Code:

Copy and paste the following VBA code into the module window.

Run the Macro:

Close the VBA editor and return to Excel.
Go to `Developer` tab (if it’s not visible, you can enable it via `File > Options > Customize Ribbon`).
Click on `Macros`, select `CreateSheetWithTodaysDate`, and run it.

Explanation of the Code:

`Date` Function: This returns the current date.
`Format` Function: Formats the date in “YYYY-MM-DD” format, making it suitable as a sheet name.
Error Handling: Before creating a new sheet, the code checks if a sheet with the same date name already exists to prevent runtime errors. If it does, it shows a message.
Worksheet Addition: If there is no existing sheet with today’s date, a new worksheet is created and named accordingly.

This macro will create a new sheet named with today’s date in your workbook every time it’s run, provided a sheet with that name doesn’t already exist.

facebooklinkedin