Excel macro examples – How to insert single or multiple worksheet based on active cell data or content – Excel VBA For Each Loop Example

About This Macro:

This macro creates multiple worksheets at once with just a single click. The worksheet names would be the names entered in a column continuously one after the other. Please note that you need to select at least one cell with data and no blank cell before running the macro. Also make sure that if you are selecting multiple cells or range then it should not contain any blank cell.

Step 1 – Launch Excel’s Visual Basic Editor

To do that just click on developer tab, then click on visual basic to launch the Visual Basic Editor (VBE)

Step 2 – Insert Module

In the Visual Basic Editor, click on insert menu and then click on module

Step 3 – Copy and paste the below code to insert single or multiple worksheet based on active cell data or content. In this example I emphasize Excel VBA For Each Loop

Sub InsertWsheet()
Dim rRange As Range
Set rRange = ActiveWindow.RangeSelection
For Each rRange In ActiveWindow.RangeSelection
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = rRange.Value
Next rRange
End Sub

Step 4 – Run the Macro

To run the macro follow any of the methods below:

Method 1: Place the cursor anywhere in the code you just added to the module and click on Run Sub/UserForm (F5) button or press the F5 key on your keyboard.

Method 2: Click on Run Menu and then click on Run Sub/UserForm (F5)

Method 3: Go to Excel’s Developer Tab and click on Macros, select the macro by name and click on run button (You may also press Alt + F8 keys to display the macros dialog)

Method 4: Go to Excel’s View Tab, click on macros and then click on view macros. Then select the macro name and click on run.