Macro to Add Multiple Worksheets in Excel

These VBA macros add worksheets in one go: as many as you ask for, one for each name in a list, or one for every month.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Add a number of sheets

The macro asks how many sheets you want and adds them at the end of the workbook:

Sub AddSheets()
    Dim n As Long, i As Long
    n = Application.InputBox("How many sheets to add?", Type:=1)
    If n < 1 Then Exit Sub
    For i = 1 To n
        Worksheets.Add After:=Worksheets(Worksheets.Count)
    Next i
End Sub

Type:=1 makes the input box accept numbers only. If you click Cancel, nothing is added.

Add sheets named from a list

Type the sheet names in a column, select them, and run this macro. Each name becomes a new sheet:

Sub AddSheetsFromList()
    Dim c As Range
    For Each c In Selection
        If c.Value <> "" Then
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = c.Value
        End If
    Next c
End Sub

Empty cells are skipped. A name that already exists, or one with characters like / or ?, stops the macro with an error.

Add one sheet for every month

This creates 12 sheets named Jan to Dec:

Sub AddMonthSheets()
    Dim m As Long
    For m = 1 To 12
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Format(DateSerial(2026, m, 1), "mmm")
    Next m
End Sub

Change "mmm" to "mmmm" for full names, such as January.

Things to know

  • Sheet names can be up to 31 characters and can’t contain \ / ? * [ ] :.
  • Every name in a workbook must be unique, so run the list or month macro on a workbook that doesn’t already have those sheets.
  • To add sheets at the start instead, change After:=Worksheets(Worksheets.Count) to Before:=Worksheets(1).

Related macros