HBN Infotech

Training – Service – Consultancy

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

Free Online Tutorials Hub


Welcome to HBN Infotech Tutorials, the best online tutorials place. Here you get free online tutorials and could learn, enhance and explore something new that you have never learnt before… Also you can download Free Microsoft Office Templates.


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


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.

Latest posts by himaghiri (see all)

Subscribe Us On YouTube

Like Our Page For Latest Tutorial Updates

Updated: April 11, 2017 — 1:09 pm

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

HBN Infotech © 2016 Frontier Theme