Create new worksheets programmatically based on values in a cell range [VBA]
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
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.
1.2 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 'Continue with next cell in cell range Next cell 'Go here if an error occurs Errorhandling: 'Stop macro End Sub
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.
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.
1.5 Excel file
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.
2.1 VBA code
Sub CreateSheetsFromList()
Dim rng As Range
Dim cell As Range
Dim Arr As Variant
On Error GoTo Errorhandling
Set 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 If
Next Value
Errorhandling:
End Sub
2.2 Excel file
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.
3.1 VBA code
Sub CreateSheetsFromDialogBox()
Dim str As String
Dim cell As Range
Dim Arr As Variant
On Error GoTo Errorhandling
Do
str = 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 If
Loop Until str = "False"
Errorhandling:
End Sub
3.2 Excel file
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.
4.1 VBA event code
'Event code that runs if a cell value is changed
Private Sub Worksheet_Change(ByVal Target As Range)
'Check if the cell value is in column B
If 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.Value
End If
'Go back to worksheet Sheet1
Worksheets("Sheet1").Activate
End Sub
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.
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.
5. Hide specific worksheets programmatically
This article demonstrates techniques to hide and unhide worksheets programmatically. The image above shows the Excel window and the worksheet tabs at the bottom of the window.
What's on this section
- Hide worksheet - VBA macro
- Where to put the code?
- How to run the macro?
- Toggle worksheet hidden/visible - VBA macro
- Link macro to button
- Hide/show multiple worksheets based on values - VBA macro
- Hide/show multiple worksheets based on array - VBA macro
- Get Excel *.xlsm file
To hide a worksheet manually press with right mouse button on with the mouse on a worksheet tab, then press with left mouse button on "Hide".
Note, hidden worksheets can easily be made visible again. Press with right mouse button on on any visible worksheet tab located at the bottom of your Excel window.
A pop-up menu appears, press with left mouse button on "Unhide".
A dialog box shows up, press with left mouse button on the worksheet you want to make visible again. Press with left mouse button on OK button to apply changes.
This article explains how to hide worksheets using Visual Basic for Applications (VBA). A macro contains VBA code. macros allow you to create very useful subroutines to minimize repetitive tasks etc.
5.1 Macro hides worksheet
This example demonstrates a macro that hides a specific worksheet.
'Name macro
Sub HideWorksheet()
'Hide worksheet named Sheet1
ActiveWorkbook.Worksheets("Sheet1").Visible = False
End Sub
To make Sheet1 visible again change False to True in the above macro.
5.2 Where to put the code?
- Press and hold shortcut key Alt, then press function key F11 once. This opens the Visual Basic Editor (VBE), shown in the image above.
Release Alt key. - Press with left mouse button on "Insert" on the top menu.
- Press with left mouse button on "Module" to create a module. This module will be shown in the Project Explorer and in this example, named Module1 which the image above also shows.
The Project Explorer shows all open workbooks, press with left mouse button on the plus sign to see the contents of a workbook. - Copy the above code.
- Paste to the module window, also shown in the image above.
- Return to Excel.
5.3 How to run the macro?
- Press and hold Alt key on your keyboard.
- Press function key F8 once. This opens the Macro dialog box, shown in the image above.
- Release Alt key.
- Press with left mouse button on the macro name you want to start.
- Press with left mouse button on "Run" button to start the selected macro.
5.4 Macro toggles worksheet hidden/visible
The following macro hides worksheet Sheet1 if visible and shows Sheet1 if hidden.
'Name macro
Sub HideWorksheet()
'Make worksheet named Sheet1 hidden if visible and vice versa
ActiveWorkbook.Worksheets("Sheet1").Visible = Not ActiveWorkbook.Worksheets("Sheet1").Visible
End Sub
5.5 Link macro to button
It is possible to create a button on your worksheet that runs the macro when the user press with left mouse button ons on it.
- Go to tab "Developer" on the ribbon. If missing search the internet for "Show developer tab" and your Excel version to find instructions on how to enable it.
- Press with left mouse button on the "Insert" button on the "Developer" tab, a pop-up menu appears.
- Press with left mouse button on the button below "Form Controls" on the pop-up menu.
- Press and hold with left mouse button where you want to place the button on the worksheet.
- Drag with mouse to size the button.
- Release the left mouse button.
- A dialog box appears to assign a macro.
- Press with left mouse button on the macro name to select it.
- Press with left mouse button on button "OK" to apply.
The image above shows a button, the round circles around the box indicates it is selected. They are called sizing handles and lets you adjust the size once again if you like.
Press and hold with left mouse button on any of the sizing handles, then drag with the mouse to resize the button. Release the left mouse button when finished.
Press with left mouse button on anywhere outside the button to deselect. To select it again press and hold CTRL key and press with left mouse button on with left mouse button on it. You start the assigned macro if you press with left mouse button on it without pressing the CTRL key.
Select the button, then press with left mouse button on the button text. A prompt appears that allows you to edit the button text.
5.6 Macro hides/show multiple worksheets based on values
The animated image above shows a button linked to a macro that hides Sheet2 and Sheet3 if the user press with left mouse button ons on the button. The worksheet names are specified in cell range B6:B7.
VBA Code
Sub ShowHideWorksheets()
Dim Cell As Range
For Each Cell In Range("B6:B7")
ActiveWorkbook.Worksheets(Cell.Value).Visible = Not ActiveWorkbook.Worksheets(Cell.Value).Visible
Next Cell
End Sub
The macro returns the following error if you try a worksheet name that doesn't exist in your workbook.
Run-time error '9':
Subscript out of range
5.7 Macro hides/shows multiple worksheets based on an array
VBA Code
Sub ShowHideWorksheets()
arr = Array("Sheet2", "Sheet3")
For Each value In arr
ActiveWorkbook.Worksheets(value).Visible = Not ActiveWorkbook.Worksheets(value).Visible
Next value
End Sub
Get the Excel file
Press with left mouse button on-a-button-to-make-specific-worksheets-hidden-or-visible-vba.xlsm
Macro category
Table of Contents Add values to a regular drop-down list programmatically How to insert a regular drop-down list Add values […]
What's on this page Auto resize columns as you type - VBA Press with left mouse button on a cell […]
Table of Contents Copy data from workbooks in folder and subfolders Move data to workbooks 1. Copy data from workbooks […]
Excel categories
21 Responses to “Create new worksheets programmatically based on values in a cell range [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
how to create a command button for show/hide columns in protect sheet?
I used the above instructions with 99% success. My sheets all have names so I copied and pasted the names of those sheets into the cell range of D7:D13. Therefore I used that range in the module in place of "B6:B7", however, for some reason, one of the sheets doesn't ever hide or unhide. Possible solutions?
how can i show and hide 3 charts on activesheet using one command button hide/unhide chart1, chart2, chart3
Sim
Sub ShowHideCharts() With ActiveSheet.ChartObjects("Chart 1") .Visible = Not .Visible End With With ActiveSheet.ChartObjects("Chart 2") .Visible = Not .Visible End With With ActiveSheet.ChartObjects("Chart 3") .Visible = Not .Visible End With End SubHi Oscar,
Thank you so much for this Macro. A simple idea but was lot more useful since it allowed the user to create only those file that are required from the unique list.
Hi- this is not working at all for me. It just creates one sheet.
I can't figure out what is wrong -
Hi!
is the length of the name more than 31 characters? That was trouble for me anyways. I used "length" to take only 31 characters since my names are still unique with only 31 characters. Then using the macro works :)
//Michael
Hi there,
i was wondering if there was any way of creating the sheets to the right side of the activesheet(sheet1).
Right now it creates the sheets to the left of it.
One question of my own though:
It creates the variables in the wrong order. Line 2 will be the last sheet created (in my case sheet 50). It still works but any tips on how to reverse it?
//Michael
@Smriti @Leissner
To insert each new sheet to the end (right), instead of previous to the working tab (left), change this line in the code:
Sheets.Add.Name = cellto this:
Sheets.Add(After:=Sheets(Sheets.Count)).Name = cellThat worked even better than expected - I had several batches and your new code made the new batches each appear as far right as possible.
Wow, you just saved me an insane amount of boring work (70 tabs to create). I used another snippet of code to alphabetize them too and then used Michael Reese's suggestion and it worked beautifully with your code. Thank you!
how to creat sheet according to month
Hi Please what if I have more than 1 row containing the same values and I want it not only to create a new row based on the cell value, but also copy the contents on the row that contains identical value into same sheet?
Hi Oscar,
I think this is a great bit of code, but my issue is that I want the tabs on my excel file to be dates that look like this: 9-1-2021, 9-2-2021,... I've figured out that this code doesn't seem to like those dashes (it only creates one blank sheet when I have the dates set up like this in the list). If I change the dates to this format 0901, 0902,... then it runs fine. Any way I can change the code to get my prefered date format?
Debra
Excelente el codigo para crear las hojas a partir de una lista. LA consulta es se puede crear la hoja con u contenido, por ejemplo una ficha para rellenar?
I get an error message saying "Can't execute code in break mode"
It shows the line "If cell <> ""Then" in red.
Do you know what is wrong here?
Hello, this was almost exactly what i was looking for. I had a question on if it was 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?
Sami Jo,
great question!
I have added another section to this post:
Copy worksheet template and rename
Hello Oscar,
I was wondering if there is a way to have new sheets being a copy of an existing sheet in all but name?
Basically we have a template that we would like to replicate across the new sheets.
Your code in the section "Copy worksheet template and rename" was almost exactly what I was looking for - it's awesome! Thank you so much for publishing this!
It also creates a copy of my template when I remove a value from column B. Is there a way to have it not do that? So have it do nothing if a value is removed?