[Excel VBA] How to Create a New Sheet with a Date and Color It

2025年12月29日

When working in Excel, you might want to automatically create a sheet with a specific date in its name and color it for better visibility. In this article, I will introduce how to easily achieve this using VBA.

What We Will Achieve

We will automate the following three tasks:

Create a sheet with a name containing today’s date.
Place the created sheet after a specific existing sheet.
Set the tab color of the created sheet to yellow.

The Actual VBA Code

Below is an example of the VBA code. This code creates a sheet named “Report2_[Date]” after the sheet named “Report1” and turns its tab yellow.

Note: Please ensure you have a sheet named “Report1” in your workbook before running this code, or change the name in the code to match your existing sheet.

Sub CreateSheetWithDateAndColor()
    Dim outputSheet As Worksheet
    Dim todayDate As String
    Dim outputSheetName As String
    Dim insertAfterSheet As Worksheet
    Dim sheetExists As Boolean
    Dim ws As Worksheet

    ' Get today's date and format it (e.g., 2024-01-01)
    todayDate = Format(Date, "yyyy-mm-dd")
    ' Define the new sheet name
    outputSheetName = "Report2_" & todayDate

    ' Identify the position to place the new sheet (after "Report1")
    Set insertAfterSheet = Nothing
    For Each ws In ThisWorkbook.Sheets
        If ws.Name = "Report1" Then
            Set insertAfterSheet = ws
            Exit For
        End If
    Next ws

    ' Error handling if the target sheet is not found
    If insertAfterSheet Is Nothing Then
        MsgBox "Sheet 'Report1' was not found. Processing stopped.", vbExclamation
        Exit Sub
    End If

    ' Check if a sheet with the same name already exists
    sheetExists = False
    For Each ws In ThisWorkbook.Sheets
        If ws.Name = outputSheetName Then
            sheetExists = True
            Set outputSheet = ws
            Exit For
        End If
    Next ws

    ' Create a new sheet if it does not exist
    If Not sheetExists Then
        Set outputSheet = ThisWorkbook.Sheets.Add(After:=insertAfterSheet)
        outputSheet.Name = outputSheetName
    End If

    ' Set the tab color of the created sheet to yellow
    outputSheet.Tab.Color = RGB(255, 255, 0) ' Yellow
End Sub

Explanation of the Code

1. Date Formatting

todayDate = Format(Date, "yyyy-mm-dd")

The Format function converts today’s date into the “yyyy-mm-dd” format (e.g., 2025-01-01). This string is used to generate the new sheet name.

2. Identifying the Position

For Each ws In ThisWorkbook.Sheets
    If ws.Name = "Report1" Then
        Set insertAfterSheet = ws
        Exit For
    End If
Next ws

The code searches for the sheet named “Report1” to place the new sheet immediately after it.

3. Checking Existence and Creating the Sheet

If Not sheetExists Then
    Set outputSheet = ThisWorkbook.Sheets.Add(After:=insertAfterSheet)
    outputSheet.Name = outputSheetName
End If

Before creating a new sheet, the code checks if a sheet with the same name already exists to avoid errors. If it does not exist, a new sheet is added.

4. Coloring the Sheet Tab

outputSheet.Tab.Color = RGB(255, 255, 0)

The .Tab.Color property is used to set the tab color to yellow (RGB: 255, 255, 0).

Summary

With this VBA code, you can easily create a new sheet with a date and color it. This is particularly useful for managing daily reports or generating dated business sheets. Please use this to improve your work efficiency.