The following macro let´s you select a cell range and then the macro creates sheets with the same names as the selected cells. Now you can easily create many sheets very quickly.
Sub CreateSheets() Dim rng As Range Dim cell As Range On Error GoTo Errorhandling Set rng = Application.InputBox(Prompt:="Select cell range:", _ Title:="Create sheets", _ Default:=Selection.Address, Type:=8) For Each cell In rng If cell <> "" Then Sheets.Add.Name = cell End If Next cell Errorhandling: End Sub
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.
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", _
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.
The end of the If statement.
Go back to the "For each" statement and store a new single cell in the cell object.
The procedure goes to this line if a line returns an error.
All procedures must end with this line.