Author: Oscar Cronquist Article last updated on September 09, 2019

This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may have multiple columns if you like.

This macro allows you to create new worksheets very quickly.

How this macro works

The animated image below shows how this macro works.

  1. Press Alt + F8 to open the Macro dialog box.
  2. Select macro CreateSheets.
  3. Click on "Run" button.
  4. An input box appears asking for a cell range.
  5. Select a cell range and click OK button.
  6. Worksheets are now added automatically to the workbook and named correspondingly after the values in the cell range.

VBA macro

'Name macro
Sub CreateSheets()

'Dimension variables and declare data types
Dim rng As Range
Dim cell As Range

'Enable error handling
On Error GoTo Errorhandling

'Show inputbox to user and prompt for a cell range
Set rng = Application.InputBox(Prompt:="Select cell range:", _
Title:="Create sheets", _
Default:=Selection.Address, Type:=8)

'Iterate through cells in selected cell range
For Each cell In rng

    'Check if cell is not empty
    If cell <> "" Then

        'Insert worksheet and name the worksheet based on cell value
        Sheets.Add.Name = cell
    End If

'Contineu with next cell in cell range
Next cell

'Go here if an error occurs
Errorhandling:

'Stop macro
End Sub

Where to put the code

  1. Copy above VBA code.
  2. Press Alt + F11 to open the Visual Basic Editor.
  3. Click on your workbook in the Project Explorer.
  4. Click on "Insert" on the menu.
  5. Click on "Module".
  6. Paste VBA code to code window, see image above.

Explaining code

Creating procedures in excel is easy. Open the Visual Basic Editor using one of these instructions:

  • Press Alt+F11
  • Go to tab Developer and click Visual basic "button"

You create macro procedures in a module. First create a module. Right click on your workbook in the project explorer. Click Insert | Module.

Sub CreateSheets()

Type: Sub CreateSheets() in the module. CreateSheets() is the name of the macro.

Dim rng As Range
Dim cell As Range

These lines declare rng and cell as range objects. A range object can contain a single cell, multiple cells, a column or a row. Read more about declaring variables.

On Error Goto Errorhandling

If the user selects something else than a cell range like a chart, this line makes the procedure go to Errorhandling.

Set rng = Application.InputBox(Prompt:="Select cell range:", _
Title:="Create sheets", _
Default:=Selection.Address, Type:=8)

The inputbox asks the user for a cell range. The cell range is stored in the range object rng.

For Each cell In rng

This stores each cell value from the range object rng to the cell object, one by one.

If cell <> "" Then

Checks if the cell variable is NOT empty. If the cell variable is empty the procedure goes to "End If" line. We can´t create a sheet with no name.

Sheets.Add.Name = cell

Creates a new sheet named with the value stored in the cell variable.

End If

The end of the If statement.

Next cell

Go back to the "For each" statement and store a new single cell in the cell object.

Errorhandling:

The procedure goes to this line if a line returns an error.

End Sub

All procedures must end with this line.

Download Excel file


* You will also get a weekly newsletter, unsubscribe anytime!

Recommended reading

List all open workbooks and corresponding sheets (vba)