Blue Flower

Adding multiple worksheets in a single stroke

This Excel tutorial contains a collection of VBA macros, to create multiple worksheets in one step. To use VBA macros, you need basic knowledge of working in VBE (Visual Basic Editor for Excel).

In certain situations when working in Excel, I believe that you had the situation that you need to create multiple worksheets with specific names for each worksheet. Of course the idea is that each worksheet has a different name. In this Excel tutorial I will show how you can use VBA macros, in one move to create multiple Worksheets whose names are listed in the 'A' column (see the picture below).

Creating Multiple Worksheets in One Step 

In the image above, notice that I have a list of all the worksheets I want to make set in the 'A' column. I also created a VBA button for all three VBA macros that are shown in this tutorial.

The VBA macros below, creates multiple worksheets, and after completion, puts an active worksheet 'master' in the foreground. The Master Worksheet is the worksheet that contains the VBA buttons. VBA macros will ignore the creation of a new worksheet if there is already a worksheet of the same name as the list.

Sub CreateNewSheetsFromList1()
'make new sheets from list and select master sheet
Dim c As Range
Dim strN As String
On Error GoTo CreateSheet
With Worksheets("Sheet1")
'for limit list from down to up find first non-empty cell in A column
For Each c In .Range(.Range("A1"), .Cells(.Rows.Count, "A").End(xlUp))
If c.Value <> "" Then strN = Worksheets(c.Value).Name
Next c
.Activate 'activate master sheet
.Range("A1").Select
End With
Exit Sub
CreateSheet:
Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = c.Value
Resume
End Sub

Create multiple Worksheets in one step and set the names of new worksheets from the list.

You can copy the VBA macro code from this Excel tutorial to your Excel Workbook, and check how many extra sheets are added in one step. In the picture below you can see the VBE window where you need to insert a new Module and copy the VBA macros from this website.

insert vba code to module in vbe for excel

The second VBA macro is similar to the previous one. The difference is that after executing the VBA macro, in the foreground, be active the last worksheet from the list in the 'A' column.

Sub CreateNewSheetsFromList2()
'make new sheets from list and select last created sheet
Dim c As Range
Dim strN As String
On Error GoTo CreateSheet
With Worksheets("Sheet1")
For Each c In .Range(.Range("A1"), .Cells(.Rows.Count, "A").End(xlUp))
If c.Value <> "" Then strN = Worksheets(c.Value).Name
Next c
End With
Exit Sub
CreateSheet:
Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = c.Value
Resume
End Sub

Insert a new worksheet with a condition or input message for the worksheet name.

The third VBA macro creates a worksheet and does not use a list. When you start a VBA macro, a popup window or incoming message will appear in which the envisaged entry field, you need to enter the name of the worksheet you want to create. After creating a new worksheet, Excel will automatically be positioned in the newly created worksheet.

Sub CreateNewSheetsFromList3()
'make new sheet and give new sheet name
Dim ActNm As String
With ActiveWorkbook.Sheets
.Add after:=Worksheets(Worksheets.Count)
End With
ActNm = ActiveSheet.Name
On Error Resume Next
ActiveSheet.Name = "Sheet1" 'master sheet
NoName: If Err.Number = 1004 Then ActiveSheet.Name = InputBox("Give name.")
If ActiveSheet.Name = ActNm Then GoTo NoName
On Error GoTo 0
End Sub

Copying a Worksheet and Creating Multiple Sheets, setting up the Sheet Name tab From the Names List

If you want to create multiple worksheets by copying a specific worksheet, create multiple new worksheets named from the list in the 'A' column, then use this VBA macro below. The While/Wend loop will run until it reaches the empty order in the name list.

Sub CopyAndInsertSheetFromList()
Dim row As Integer
Dim newname As String
Sheets("Sheet1").Select
row = 1
newname = Sheets("Sheet1").Cells(row, 1).Value
While newname <> "" 'start loop
MsgBox row & " " & newname
Sheets(row + 1).Select
Sheets(row + 1).Copy After:=Sheets(row + 1)
ActiveWorkbook.Sheets(row + 2).Name = newname
row = row + 1
newname = Sheets("Sheet1").Cells(row, 1).Value
Wend 'end loop
End Sub

Copying a specific worksheet for all days of the month (31 times)

Creating a multiple copy of a given worksheet template can also be used for dates in the month where each new worksheet will be named by the number of days. If you do the calculations for each day of the month then you need to create a template for one day (the first day of the month). Using the VBA macro below, Excel will copy the template 31 times and so create 31 worksheet per month in one step, with the already prepared data on the worksheet that serve as template.

Creating worksheets for month dates using VBA in one step

Sub CopySheetToMultipleSheets()
'Multiple copy of a worksheet named 1 in 31 copies
Dim sh As Integer
Application.ScreenUpdating = False
For sh = 2 To 31 '31 indicates how many copies will be
Sheets("1").Copy After:=Sheets(sh) 'We copied a worklist named as number 1
Sheets(sh + 1).Name = Str(sh) 'each new copy increases the number for 1
Next sh 'slijedeća kopija u petlji
Application.ScreenUpdating = False
End Sub

Creating new worksheets based on the list in a specific cell range

The following VBA macros will create new worksheets from the list in the cell range, starting with a worksheet in the 'C5' cell.Note that the basic worksheet was named "Master". This worksheet contains a list of names to be named newly created worksheets.On this worksheet, you can place VBA buttons to run VBA macros.

Creating worksheets with names that are in the list at once

This VBA macro below is pre-embedded a specific range of cells containing a list of new worksheets.

Sub AddWorksheetsfromList1()
'specified range
Dim cell As Excel.Range
Dim wsWithSheetNames As Excel.Worksheet
Dim wbToAddSheetsTo As Excel.Workbook
Application.ScreenUpdating = False
Set wsWithSheetNames = ActiveSheet
Set wbToAddSheetsTo = ActiveWorkbook
For Each cell In wsWithSheetNames.Range("C5:C9") 'specific range list names of sheets which will be create
With wbToAddSheetsTo
.Sheets.Add After:=.Sheets(.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = cell.Value
If Err.Number = 1004 Then
Debug.Print cell.Value & " already used as a sheet name"
End If
On Error GoTo 0
End With
Next cell
Sheets("Master").Activate
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

To create worksheets from the list, using this VBA macro below, before selecting the VBA procedure, you need to select the correct cell range that contains the list of workbook names. So from the first to the last cell containing the worksheet name.

Sub AddWorksheetsfromList2()
'select list range before running VBA procedure
Dim cell As Excel.Range
Dim wbToAddSheetsTo As Excel.Workbook
Application.ScreenUpdating = False
Set wbToAddSheetsTo = ActiveWorkbook
For Each cell In Selection
With wbToAddSheetsTo
.Sheets.Add After:=.Sheets(.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = cell.Value
If Err.Number = 1004 Then
Debug.Print cell.Value & " already used as a sheet name"
End If
On Error GoTo 0
End With
Next cell
Sheets("Master").Activate
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

This VBA macro below, creates new worksheets from the list that contains worksheet names starting in the 'C5' cell. The VBA macro below uses the For/Next loop to find the last filled order in which the name list is located.

Sub AddWorksheetsfromList3()
'This VBA macro will create separate Sheet tabs based on a list in Master Sheet in C5 and down
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("Master").Range("C5")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'create a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value 'rename the new worksheet based on list
Next MyCell
Sheets("Master").Activate
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Copying the "Template" worksheet and creating a new worksheet, as well as automatic name setting with today's date

If you often need to create a new worksheet that should be identical to the copy of the template, and the name of the newly created worksheet should be the date, then use the VBA macro below. The 'Template' worksheet contains the organization of the data we use every day. Worksheet 'Master' is the initial list of our workbook where you run the VBA macro. After you start VBA macro, Excel will duplicate the 'Template' worksheet and set a new name with today's date. If there is already a workbook with today's date, Excel will simply copy the 'Template' worksheet and add the number.

Automatically add a new worksheet in the workbook and naming with today's Date

Sub CreateNewTabSheetNameWithTodayDate()
' Create New Sheet tab with current date
On Error Resume Next
Sheets("Template").Copy After:=Sheets("Master")
ActiveSheet.Name = Format(Date, "dd.mm.yy")
'ActiveSheet.Name = Format(Date, "dd.mmm.yy") 'another format
'ActiveSheet.Name = Format(Date, "dd.mm.yyyy") 'another format
On Error GoTo 0
End Sub

Finally, in the attachment of this tutorial, you have the download file option on which I did this tutorial. To use VBA macros from this tutorial, you should copy the VBA macro in the sample files from this VBA site to your Excel.