How to Add And Name A New Sheet At The Same Time Using Excel VBA

Published on February 24, 2017
image_pdfimage_print

Well, I must say that summer is already starting to scare me here in Kolkata, India. It’s still February and the temperatures have started to soar (Oh God! Save me in May). 

Anyways, today I would be posting the first post on Mad About Excel that relatevba5s to Excel VBA. VBA, for those who don’t know, stands for Visual Basic for Applications, which is nothing but the language in which the Microsoft Office packages have been programmed. Each of these packages has its own VBA language and so the one that we would be dealing with in this and the other posts on Mad About Excel would, of course, be Excel VBA.

Excel VBA is the most advanced of all the topics in excel, and if you really want to become an excel wizard, I urge you to learn this. Learning Excel VBA will literally transform the way you use excel.

Now let’s dive into today’s topic.

Adding And Naming A New Sheet At The Same Time Using Excel VBA

You may be having a situation at work, where you need to make several new workbooks daily – all having the sheets having the same names. This can be very frustrating to do for each workbook, especially if the number of worksheets is large.

In this post, I am going to give you a simple VBA code which you can use to make your life much easier. The steps are given below:

  1. Open a workbook and press Alt + F11 to open the VBA Editor.

2. Click the Insert Menu and then click ‘Module’. A blank space will appear where you can write the macro.

3. Type “Sub insertsheet” and press Enter. (‘insertsheet’ is the name of the macro. You can choose a different name if you want)

4. Then copy the code given below:

Worksheets.Add(After:=Worksheets(“Sheet1”)).Name = “Attendance”

vba1

In this code Sheet1 is the name of the worksheet after which you want to insert a new worksheet, and Attendance is the name that you want to give to the new worksheet. Replace these with appropriate names.

5. Then press the F5 key or press the arrow button (see screenshot) to run the macro. A new sheet named       ‘Attendance’  will be inserted after the sheet named ‘Sheet1’

vba3

6. You can repeat this code by replacing the names of the worksheets and you will be able to insert multiple sheets using just a click of a button. So say you want to insert 3 sheets in total, named ‘Attendance’, ‘Work Timings’, and ‘Employee List’, after Sheet1 in a new workbook. The code for that purpose would be:

Sub insertsheet()
Worksheets.Add(After:=Worksheets(“Sheet1”)).Name = “Attendance”
Worksheets.Add(After:=Worksheets(“Attendance”)).Name = “Work Timings”
Worksheets.Add(After:=Worksheets(“Work Timings”)).Name = “Employee List”
End Sub

Now if you run the macro, all these 3 sheets will be inserted automatically in the file.

vba4

Now, how cool is that!

That is it for today. Please use the comment box below and tell me how you liked today’s post. I will be posting on Excel VBAs frequently now as I want my readers to leverage the power of this amazing feature that microsoft offers.

I will be posting on Excel VBAs frequently now. I want my readers to leverage the power of this amazing feature that microsoft offers.

Now let me go and find some respite under the shower.

Have a great day and…Happy Excelling.

Click here to follow us on Facebook.

 

GET MY EXCEL GUIDE FOR FREE
COMPLETE EXCEL FUNCTIONS AND SHORTCUTS GUIDE

Subscribe to our newsletter and get my Complete Excel Functions and Shortcuts Guide for FREE!

Thank you for subscribing.

Something went wrong.

HUGE LIMITED TIME DISCOUNT ON THE BEST ADVANCED EXCEL COURSE ENROLL NOW!!
excel_look_professional
Making Excel Worksheets Look Professional – A Case Study
14 Excel tips
14 Excel Tips – Make Yourself Super-Efficient in Excel
DATA CLEANING EXCEL
Ultimate Guide to Data Cleaning in Excel – 11 Super-Powerful Data Cleaning Techniques
RANDOMIZE LIST RANDBETWEEN
Randomize a List in Excel Using RANDBETWEEN Function
FREEZE PANES
Freeze Panes in Excel – The Complete Guide
HOW TO LEARN EXCEL
How to Learn Excel and Be a Spreadsheet Champion
BEST BOOKS COURSES EXCEL VBA
Best Books and Courses to Learn Excel VBA
BECOME AN EXCEL NINJA COMPLETE REVIEW
Become An Excel Ninja – Course Review
BEST EXCEL COURSES
10 Best Online Courses to Make You an Excel Wizard
BEST BOOKS COURSES EXCEL VBA
Best Books and Courses to Learn Excel VBA
How to Sort Excel Worksheet in Alphabetical Order
How to Sort Excel Worksheets in Alphabetical Order
VBAPOST1
How to Add And Name A New Sheet At The Same Time Using Excel VBA
ROUNDING NUMBERS IN EXCEL
Rounding Numbers in Excel – The Complete Guide
number_of_words
How to Find the Number of Words in a Text String in Excel
HOW TO FIND IF A TEXT STRING CONTAINS SPECIFIC TEXT IN EXCEL
How to Find if a Text String Contains Specific Text in Excel
How To Indentify ULP case in a text string
How To Identify If A Text String Is In Upper, Lower Or Proper Case
Pivot Tables
How To Convert Pivot Table Into Frequency Distribution
How to Convert Pivot Table into a Frequency Distribution in Excel

GET MY EXCEL GUIDE FOR FREE
COMPLETE EXCEL FUNCTIONS AND SHORTCUTS GUIDE

Subscribe to our newsletter and get my Complete Excel Functions and Shortcuts Guide for FREE!

Thank you for subscribing.

Something went wrong.

Share
Tweet
+1
Pin
Share
Stumble
Enjoyed this video?
VBAPOST1
"No Thanks. Please Close This Box!"