Create new sheet based on cell value: Excel, macro

Excel allows you quite a lot of freedom to customize your user experience via the use of macros. This tutorial will show you how to create and name worksheets using an existing list via a macro.

You are going to quickly become a pro in a few minutes, you will learn how to automate the sheet creation process programmatically:

• Open the Visual Basic Editor, press Alt+F11
• In the new window, click on Insert > Module, you will see a window with modules on the left, and the code editor on the right
• To create the macro, paste the following piece of code in the code editor:

'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

• Go back to your Excel sheet
• Open the macro dialog box, press Alt+F8
• Select the macro named "CreateSheets", click on "CreateSheets"
• Launch the macro, click on Run
• In the new dialog box, either use your mouse to select a range of cells or manually enter it (for example $A$1;$A$14)
• To create all the worksheets, click on Ok

Here you go, you now have created a macro, used it, and programmatically automated a worksheet creation automation process.