[Excel VBA] How to Bulk Create Sequential Worksheets Using For Loops

Introduction

Manually preparing 12 sheets for monthly reports (e.g., “April”, “May”…) or separate summary sheets for each branch (e.g., “Branch01”, “Branch02″…) can be very tedious.

By combining the VBA For...Next loop with the Worksheets.Add method, you can instantly automate the creation of such sequential sheets.

In this article, I will explain how to bulk add a specified number of worksheets with sequential names to the end of your workbook.

Completed VBA Code

The following code adds 12 sheets named “Report_1” through “Report_12” to the end of the workbook.

Sub CreateMultipleSheets()

    ' Declare variables
    Dim i As Long
    Dim sheetCount As Long
    Dim sheetNameBase As String
    
    '--- Settings ---
    sheetCount = 12 ' Number of sheets to create
    sheetNameBase = "Report_" ' Sheet name prefix
    '--- End Settings ---

    ' Temporarily turn off screen updating to speed up processing
    Application.ScreenUpdating = False

    ' --- Repeat the process for the specified number of times using a For loop ---
    For i = 1 To sheetCount
    
        ' 1. Add a new sheet to the end of the workbook
        '    Identify the last sheet using Worksheets.Count and place the new one "After" it
        ThisWorkbook.Worksheets.Add After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        
        ' 2. Rename the newly added sheet
        '    The sheet added by the .Add method becomes active, so you can manipulate it with ActiveSheet
        ActiveSheet.Name = sheetNameBase & i
        
    Next i
    
    ' Turn screen updating back on
    Application.ScreenUpdating = True

    MsgBox sheetCount & " sheets have been created."

End Sub

Key Points of the Code

1. For...Next Loop

For i = 1 To sheetCount
    ' ... Process to repeat ...
Next i

The For...Next statement is fundamental for performing repetitive tasks in VBA. By writing i = 1 To sheetCount, the code repeats the process while increasing the counter variable i by 1, from 1 up to sheetCount (12 in this example).

2. Adding Sheets to the End

ThisWorkbook.Worksheets.Add After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

By specifying the last sheet of the workbook in the After argument of the Worksheets.Add method, the new sheet is always added to the far right. ThisWorkbook.Worksheets.Count returns the total number of current sheets, so ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) always refers to the last sheet.

3. Setting Sequential Names

ActiveSheet.Name = sheetNameBase & i

The sheet added by Worksheets.Add becomes the active sheet (ActiveSheet) at that moment. We assign a string to its .Name property by concatenating the prefix (sheetNameBase) and the loop counter variable i using the & operator. This results in names like “Report_1”, “Report_2”, “Report_3″… as the loop runs.

Advanced: Using Zero-Padding (01, 02…)

If you want to align the digits like “01, 02…” instead of “1, 2…”, use the Format function.

Change the part of the code that names the sheet as follows:

' Before
' ActiveSheet.Name = sheetNameBase & i

' After: Convert the number to "00" format using the Format function
ActiveSheet.Name = sheetNameBase & Format(i, "00")

Format(i, "00") converts the number i into a 2-digit string, padding with a zero on the left if it is a single digit. This allows you to create names like “Report_01”, “Report_02”, … “Report_12”.

Summary

Bulk creation of sequential sheets is a typical use case for For loops.

• Create the loop structure with For i = 1 To NumberOfSheets.
• Use Worksheets.Add After:=... inside the loop to always add sheets to the end.
• Set the .Name property of the added ActiveSheet by combining the prefix and the loop variable i.

By remembering this pattern, you can significantly improve the efficiency of tedious sheet preparation tasks.