HBN Infotech

Training – Service – Consultancy

Excel macro examples – How to insert single or multiple worksheet based on active cell data or content

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.

excel vba for each

Similar Tutorial: Excel Tutorial VBA Macros – How to create a number chart 1 to 100 using Excel macro – Excel Macro Examples Series – Excel VBA For Next Loop Example

himaghiri

Founder at HBN Infotech
Himaghiri Thanayan.N is the founder of HBN Infotech. He has great dreams and passion for Graphic Design, Web Design, CAD Design, Arts & Crafts, Science & Information Technology and Article Writing.
Updated: April 11, 2017 — 1:09 pm

Leave a Reply

Your email address will not be published. Required fields are marked *

7 + 20 =

HBN Infotech © 2016 Frontier Theme