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.
Press Alt + F8 to open the Macro dialog box.
Select macro CreateSheets.
Click on "Run" button.
An input box appears asking for a cell range.
Select a cell range and click OK button.
Worksheets are now added automatically to the workbook and named correspondingly after the values in the cell range.
'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", _
'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
'Continue with next cell in cell range
'Go here if an error occurs
Where to put the code
Copy above VBA code.
Press Alt + F11 to open the Visual Basic Editor.
Click on your workbook in the Project Explorer.
Click on "Insert" on the menu.
Click on "Module".
Paste VBA code to code window, see image above.
Creating procedures in excel is easy. Open the Visual Basic Editor using one of these instructions:
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.
Type: Sub CreateSheets()in the module. CreateSheets() is the name of the macro.