Author: Oscar Cronquist Article last updated on October 16, 2022
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.
Table of Contents
- Create new worksheets programmatically based on values in a cell range[VBA]
- How this macro works
- VBA macro
- Where to put the code
- Explaining code
- Get Excel file
- Create new worksheets based on a comma delimited list [VBA]
- VBA code
- Get Excel file
- Create new worksheets using an Input box [VBA]
- VBA code
- Get Excel file
- Copy worksheet template and rename [VBA]
1. Create new worksheets programmatically based on values in a cell range
1.1 How this macro works
The animated image above shows how this macro works.
- Press Alt + F8 to open the Macro dialog box.
- Select macro CreateSheets.
- Press with mouse on "Run" button.
- An input box appears asking for a cell range.
- Select a cell range and press with left mouse button on the "OK" button.
- Worksheets are now added automatically to the workbook and named correspondingly after the values in the cell range.
Back to top
1.2 VBA macro
'Name macroSub CreateSheets()'Dimension variables and declare data typesDim rng As RangeDim cell As Range'Enable error handlingOn Error GoTo Errorhandling'Show inputbox to user and prompt for a cell rangeSet rng = Application.InputBox(Prompt:="Select cell range:", _Title:="Create sheets", _Default:=Selection.Address, Type:=8)'Iterate through cells in selected cell rangeFor 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'Continue with next cell in cell rangeNext cell'Go here if an error occursErrorhandling:'Stop macroEnd Sub
Back to top
1.3 Where to put the code
- Copy above VBA code.
- Press Alt + F11 to open the Visual Basic Editor.
- Press with mouse on your workbook in the Project Explorer.
- Press with mouse on "Insert" on the menu.
- Press with mouse on "Module".
- Paste VBA code to code window, see image above.
Back to top
1.4 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 press with left mouse button on Visual basic "button"
You create macro procedures in a module. First create a module. Press with right mouse button on on your workbook in the project explorer. Press with left mouse button on 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.
Back to top
1.5 Excel file
Get the Excel file
Quickly-create-mulitple-sheets.xlsm
Back to top
Recommended reading
List all open workbooks and corresponding sheets (vba)
2. Create new worksheets programmatically based on a comma-delimited list
The image above shows a comma delimited list in cell B2, the macro below in section 2.1 lets you select a cell containing a comma delimiting list.
It splits the string based on the comma into an array of values. The values are then used to insert new worksheets with names based on those array values.
Back to top
2.1 VBA code
Sub CreateSheetsFromList()Dim rng As RangeDim cell As RangeDim Arr As VariantOn Error GoTo ErrorhandlingSet rng = Application.InputBox(Prompt:="Select cell:", _Title:="Create sheets", _Default:=Selection.Address, Type:=8)Arr = Split(rng.Value, ", ")For Each Value In Arr If Value <> "" Then Sheets.Add.Name = Value End IfNext ValueErrorhandling:End Sub
Back to top
Where to put the code?
2.2 Excel file
Get the Excel file
Quickly-create-mulitple-sheetsv2.xlsm
Back to top
3. Create new worksheets using an Input box
The following macro displays an input box allowing the Excel user to type a worksheet name, the worksheet is created when the "OK" button is pressed.
The macro stops if nothing is typed or the user presses the "Cancel" button. It shows a new input box each time a new worksheet is created.
Back to top
3.1 VBA code
Sub CreateSheetsFromDialogBox()Dim str As StringDim cell As RangeDim Arr As VariantOn Error GoTo ErrorhandlingDostr = Application.InputBox(Prompt:="Type worksheet name:", _Title:="Create sheets", Type:=3) If str = "" Or str = "False" Then GoTo Errorhandling: Else Sheets.Add.Name = str End IfLoop Until str = "False"Errorhandling:End Sub
Where to put the code?
Back to top
3.2 Excel file
Get the Excel file
Quickly-create-mulitple-sheetsv3.xlsm
Back to top
4. Copy the worksheet template and rename
This example demonstrates an Event macro that copies a worksheet based on a value in cell E2 and renames it to a cell value in column B.
Is it possible to not just generate the new sheet from typing in a cell and name the sheet after the cell but to have the new sheet be a copy on a current sheet that is kind of like a template for a form?
4.1 VBA event code
'Event code that runs if a cell value is changedPrivate Sub Worksheet_Change(ByVal Target As Range)'Check if the cell value is in column BIf Not Intersect(Target, Range("B:B")) Is Nothing Then 'Copy worksheet based on value in cell E2 in worksheet Sheet1 and put it last Sheets(Worksheets("Sheet1").Range("E2").Value).Copy , Sheets(Sheets.Count) 'Rename worksheet to the value you entered. ActiveSheet.Name = Target.ValueEnd If'Go back to worksheet Sheet1Worksheets("Sheet1").ActivateEnd Sub
Back to top
4.2 Where to put the event code?
- Press Alt + F11 to open the Visual Basic Editor.
- Double press with left mouse button on with the left mouse button on the worksheet name where you want to put the event code, in the worksheet Explorer shown in the image above.
- Paste event code to the code window, also shown in the image above.
- Return to Excel.
Remember to save the workbook as a macro-enabled workbook *.xlsm in order to attach the code to the workbook.
Back to top
4.3 How to run macro?
The event code runs whenever a new value is entered in column B. For example, type Trend in cell B5, then press Enter.
The macro automatically copies the worksheet "Template" given in cell E2 and renames it to Trend. That is all.
Back to top
Macro category
Add or remove a value in a drop down list programmatically
This article demonstrates how to add or remove a value in a regular drop down list based on a list […]
Add values to a regular drop-down list programmatically
Table of Contents Add values to a regular drop-down list programmatically How to insert a regular drop-down list Add values […]
Add values to a two-dimensional table based on conditions [VBA]
This article demonstrates how to place values automatically to a table based on two conditions using a short macro. Cell […]
More than 1300 Excel formulas
Excel categories
Home page
Excel Functions
Excel Formulas
Excel Tables
Advanced Filter
Data Validation
Drop Down List
Named Ranges
Excel Solver
Charts
Conditonal Formatting
Pivot Tables
VBA
Macros
UDFs
Archive
Comments (17)