Hello Excellers!. Today I want to share with you a small but very handy Excel Macro, but aren’t they all really handy?. I need to add one worksheet to multiple workbooks, well a lot of workbooks. The worksheet needs to be called the same in every workbook. so, why not write a piece of VBA code to do the job for me?. Repetitive tasks are the ideal scenario for using macros. Write the code once then repeat as many times as I want to use it over and over again.
What Does The Macro Do?
The macro inserts a new worksheet into the active workbook and then renames that worksheet. In this example, I want to name the worksheet ‘Invoice Summary’. We get Excel to check that there is not already a worksheet with that name if there is then a warning is displayed to the user. This is a little extra step just to help with error checking.
How Does The Macro Work?
In the first place- Insert a new Module (I am inserting this module in my Personal Macro Workbook to store the macro).
Step 1. We need to declare any variables. In this macro, it is just the one, ws As Worksheet. This simply means that Excel allocates some memory to enable it to store this information and represent this value.
Step 2. We are going to use some error checking in this macro. This piece of the code instructs Excel what to do if there is an error.
Step 3. This is where Excel inserts the new worksheet.
Step 4. Excel then names the new worksheet what you specify. In this example want to name the worksheet Invoice Summary.
Step 5. If there is an error, which would be caused by a worksheet with the worksheet name already existing we get Excel to display a message box and inform the user that there is already a worksheet with the name we specified in Step 4.
The Macro ends at this stage. I have saved this macro in my Personal Macro workbook which means I can reuse the VBA code in any workbook I want to. This is really useful as I needed to insert the same Invoice Summary worksheet a large number of workbooks.
Do you want to copy the VBA code?.
[stextbox id=”grey”]
Sub InsertWorksheet()
‘macro by How To Excel At Excel.Com
‘1. Declare variable for our worksheet
Dim ws As Worksheet
‘2. Instruct Excel what to do if there is an error
On Error GoTo MyError
‘3.Insert the worksheet
Set ws = Sheets.Add
‘4. Name the worksheet
ws.Name = “Invoice Summary”
‘5. Display the error message if an error occurred
MyError:
MsgBox “There is already a sheet called that.”
End Sub[/stextbox]
What Next?.
You can check out my full list of Macro Mondays blog posts, and the code is always free for you to copy at the end of the blog post.
If you want more tips then sign up for my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Next Steps?. Do You Want To Learn How Write Excel Macros? Check Out Chandoo’s VBA Classes.
