Once you start learning VBA one of the coolest things you can do is to write a VBA code to insert new a worksheet in a workbook.
10 Useful Examples of Macros for Accounting: 1. Macro: Save All Helpful for saving all open excel workbooks at once. It is advised to run this macro before running other macros or performing tasks that’s could potentially freeze Microsoft Excel. How to update an Excel spreadsheet using ADO An example can be found at the bottom of the post. Option Explicit 'Purpose: Updates the contents of an Excel Spreadsheet using ADO 'Inputs: sWorkbookPath The path of the workbook to update the range contents of. ' sRange The range name or range reference to update (eg.
Well, there is already a shortcut key to insert a new worksheet or you can also use the normal option but the benefit of using a VBA code is you can add multiple worksheets with a single click and you can also define that where you want to add it.
For this, you need to use the Sheets.Add method, and in this post, we will be learning how to use it to add one or more worksheets in a workbook.
Excel Vba Macro Examples
Sheets.Add Method
- Before: To add a new sheet before a sheet.
- After: To add the new sheet before a sheet.
- Count: Number of sheets to add.
- Type: Type of the sheet you want to add (LINK)
Write a VBA Code to ADD a New Sheet in a Workbook
Open the visual basic editor and follow these steps.
- First, you need to enter Sheets.Add method.
- Then you need to define the place to add the new sheet (Before or After).
- Next thing is to enter the count of worksheets.
- In the end, the type of sheet.
Different Ways to Add New Sheets in a Workbook using a VBA Code
Below you have different ways to add a new sheet in a workbook:
1. Add a Single Sheet
To add a single sheet, you can use the below code, where you didn’t specify any argument.
This code tells Excel to add a sheet in the active workbook, but as you don’t have any argument it will use the default values and add one worksheet(xlWorksheet) before the active sheet.
Here’s one more way to write this, check out the below code.
As you are already in the active workbook you can use the below code as well. It does the same thing.
2. Add Multiple Sheets
To add multiple sheets in one go, you just need to define the COUNT argument with the number of sheets you want to add.
Now the count of the sheets that you have defined is 5, so when you run this code it instantly adds the five new sheets in the workbook.
3. Add a Sheet with a Name
If you want to rename the sheet after adding it, you can use the following code:
In the above code, we have used the name object (LINK) which helps you to specify the name of a sheet.
4. Add a Sheet with a Name from a Cell
You can also take the value to use as the sheet’s name from a cell.
In the above code, cell A1 is used to get the name for the new sheet.
5. Add a Sheet After/Before a Specific Sheet
As these arguments are already there in the Sheets.Add where you can specify the sheet to add a new sheet before or after it.
Now in the above code, you have two lines of code where you have used before and after an argument in the Sheet.Add method. So, when you run this code it adds two sheets one is before and one is after the “mySheet”.
6. Add a New Sheet at Beginning
By using the before argument using you can also add a sheet at the beginning of the sheets that you have in the workbook.
So basically, what we are going to do is we’re going to specify the sheet number instead of the sheet name.
In the above code, you have used the sheet number (1) that tells VBA to add the sheet before the sheet which is on the first position in all the worksheets. In this way, it will always add the new sheet at the beginning.
7. Add a New Sheet at End (After the Last Sheet)
To add a new sheet in the end you need to write the code in a different way. So, for this, you need to know how many sheets there in the workbook are so that you can add a new sheet at the end.
In the above code, Sheet.Count returns the count of the sheets that you have in the workbook, and as you have defined the after argument it adds the new sheet after the last sheet in the workbook.
8. Add Multiple Sheets and use Names from a Range
The following code counts rows from the range A1:A7. After that, it loops to add sheets according to the count from the range and use values from the range name the sheet while adding it.
But with the above code, there could be a chance that the sheet name you want to add already exists or you have a blank cell in the name range.
In that case, you need to write a code that can verify if the sheet with the same name already exists or not and the cell from where you want to take the sheet name is blank or not.
If both conditions are fulfilled only then it should add a new sheet. Let me put it in steps two steps:
First, you need to write an Excel User Defined Function to check if a sheet with the same name already exists or not.
Second, you need to write a code using this function and that code should also check if the name cell is blank or not.
Now in the above code, you have used the VBA IF Statement and in this statement, you have the sheet check function which checks for the sheet name and then you have a condition to check if the name cell has a blank value.
More Tutorials on Worksheets
What are Macros?
They are a series of commands used to automate a repeated task. This can be run whenever the task must be performed.
How to access Macros
Click on the ‘View’ tab, at the end you’ll find the function ‘Macros’ arranged in the Macros group. Click the arrow under ‘Macros’ where you can manage your macro performances easily.
To edit a macro, click on the ‘Edit’ button, this will take you to the ‘Visual Basic Editor’ where you can easily modify the macro to do what you want.
Visual Basic Editor view:
10 Useful Examples of Macros for Accounting:
1. Macro: Save All
Helpful for saving all open excel workbooks at once. It is advised to run this macro before running other macros or performing tasks that’s could potentially freeze Microsoft Excel.
Name this macro ‘SaveAll’
Example code:
2. Macro: Comments and Highlights
Helpful is you use a lot of comments and highlights while editing worksheets. Running this macro generates a new tab at the front of the worksheet with a listing of every cell with a comment or highlight in the workbook. Each cell reference is a hyperlink that leads directly to the cell with the comment/highlight. The summary tab lists the value within the cell and the text in the comment.
Additionally, there is an ‘Accept’ button that removes the highlight or deletes the comment from the chosen cell. Once all changes are accepted, the summary tab will be deleted.
NB: This macro only sets up to find certain highlights of yellow, but this can be modified.
3. Macro: Insert a Check Mark
Helpful for footing (Adding a column of numbers) trial balances, schedules and reconciliations. It is often common to put a check mark below the total to show the column total is accurate.
This macro will insert a red check mark in the active cell. Once this is added all you will be required to do is select the cell you want to check-mark in and run the macro. Name this macro ‘Checkmark’.
Example code:
4. Macro: Mail Workbook
Helpful to send a lot of excel files via email. Running this macro creates a new Microsoft Outlook email with the last saved version of the open workbook. The email, by default, will not be addressed by anyone, the subject will be the name of the workbook, and the body will be ‘See attached’. However, these settings can be customized.
Example code:
5. Macro: Copying the Sum of Selected Cells
Helpful in copying the sum of several cell in a spreadsheet. These cells can be scattered throughout the spreadsheet or all in the same row/column.
Once this macro is added you need to highlight the cells you want to sum, then run the macro, and then paste into the cell you want the sum in.
NB: This will just paste the value of the sum, not a sum() formula
Example code:
6. Macro: Open Calculator
Helpful to open a calculator.
Example code:
Sub OpenCalculator() |
7. Macro: Refresh All Pivot Tables
Example Visual Basic Code For Excel Data
Helpful to refresh all pivot tables in the whole workbook in a single shot.
Example code:
8. Macro: Multiply all the Values by a Number
Helpful if you have a list of numbers you want to have multiplied by a particular number. Select the range of cells you need and run the example code below. It will first ask you for the number with whom you want to multiply and then instantly multiply all the numbers in the range with it.
Example Of Vba Code
Example code:
Visual Basic Code Examples For Excel Pdf
9. Macro: Add a Number to all the Numbers in Range
Similar to multiplying, you can also add a number to all the numbers in a particular range.
Microsoft Excel Visual Basic Examples
Example code:
10. Macro: Remove Negative Signs
Excel Visual Basic Programming Examples
Code that checks a selection and converts all the negative number into positive. Select a range and run the code.
Example code: