Sabrina Ayon
While working with Microsoft Excel, we may have to work with lots of data. We may build many spreadsheets when dealing with a lot of information in order to arrange the workbooks and make it simpler to discover information. We can add sheets using Excel VBA. It helps the user to customize the application with just some simple codes. In this article, we will see some examples of excel vba add sheet with variable name.
Download Practice Workbook
You can download the workbook and practice with them.
5 Ideal Examples of Excel VBA to Add Sheet with Variable Name
When we want to add a sheet, for every particular sheet we need to write the variable name of that sheet. With the help of VBA in excel, we can easily add sheets with variable names by just running simple codes. We have the following dataset and the name of the sheet is List. Now, we want to add more sheets with the variable names.
1. Use Simple Excel VBA Code to Add Sheet with Variable Name
With Excel VBA, users can easily use the code which acts as excel menus from the ribbon. To use the VBA code to add sheet with variable name, let’s follow the procedure.
STEPS:
- Firstly, go to the Developer tab from the ribbon.
- Secondly, click on Visual Basic from the Code category to open the Visual Basic Editor. Or press Alt + F11 to open the Visual Basic Editor.
- Instead of doing this, you can just right-click on your worksheet and go to View Code. This will also take you to Visual Basic Editor.
- This will appear in the Visual Basic Editor where we write our codes to create a table from range.
- Thirdly, click on Module from the Insert drop-down menu bar.
- This will create a Module in your workbook.
- And, copy and paste the VBA code shown below.
VBA Code:
Sub Add_Sheets_with_Variable_Name1()
Sheets.Add.Name = "List2"
End Sub- After that, run the code by clicking on the RubSub button or pressing the keyboard shortcut F5.
- This will add sheet with the variable name List2.
Read More: How to Add Sheet with Name in Excel VBA (6 Easy Ways)
2. Excel VBA to Add Sheet Before Another Sheet with Variable Name
Let’s see another example of adding a sheet before another sheet with variable name using Excel VBA.
STEPS:
- In the first place, go to the Developer tab from the ribbon.
- Second, click on Visual Basic to open the Visual Basic Editor.
- Another way to open the Visual Basic Editor is simply to press Alt + F11.
- Alternatively, right-click on the sheet, then select View Code.
- Next, go to Insert and select Module from the drop-down menu.
- And, this will open up the visual basic window.
- After that, copy and paste the VBA code below.
VBA Code:
Sub Add_Sheets_with_Variable_Name2()
Sheets.Add(Before:=Sheets("List2")).Name = "List3"
End Sub- Further, press the F5 key or click on the Run Sub button to run the code.
- And, finally you will be able to add a sheet with variable name List3.
Read More: How to Add Sheet with Name from Cell Using Excel VBA
3. Add Sheet after Another Sheet with Variable Name in Excel VBA
Let’s look at another example of utilizing Excel VBA to add sheet after another sheet with the variable name.
STEPS:
- To begin, click the Developer tab on the ribbon.
- Second, launch the Visual Basic Editor by clicking on Visual Basic.
- Alternatively, you may access the Visual Basic Editor by pressing Alt + F11.
- Or, right-click on the sheet and choose View Code from the menu.
- Next, pick the Module from the drop-down box under Insert.
- And the visual basic window will appear.
- Write the code there.
VBA Code:
Sub Add_Sheets_with_Variable_Name3()
Sheets.Add(After:=Sheets("List")).Name = "List1"
End Sub- Finally, press the F5 key to run the code.
- Lastly, you will be able to add a sheet with variable name List1.
Read More: Excel Macro: Create New Sheet and Rename (3 Ideal Examples)
4. Use VBA to Get Sheet with Variable Name from a Cell
Let’s have a glance at another way to add a sheet with variable name from a cell using Excel VBA. Suppose, we want to use cell C5. And, the name of the sheet will be Skin Care as we can see in the screenshot.
STEPS:
- To start, open the ribbon and select the Developer option.
- Then, to access the Visual Basic Editor, click on Visual Basic.
- Pressing Alt + F11 will also bring up the Visual Basic Editor.
- Alternatively, right-click the sheet and choose View Code from the menu that appears.
- Now, from the Insert drop-down option, pick Module.
- Then copy and paste the VBA code that follows.
VBA Code:
Sub Add_Sheets_with_Variable_Name4()
Sheets.Add.Name = Range("C5")
End Sub- Run the code by pressing the F5 key.
- And, that will add a sheet and the sheet variable name will be the name which is in the cell C5 and the name is Skin Care.
Read More: Macro to Create New Sheet and Copy Data in Excel (4 Examples)
5. Apply VBA to Add Sheet with Variable Name at Beginning
Now, take a look at another Excel VBA method for creating a table from a range.
STEPS:
- To begin, open the ribbon and choose Developer from the drop-down menu.
- Then select Visual Basic to open the Visual Basic Editor.
- The Visual Basic Editor may also be accessed by pressing Alt + F11.
- Alternatively, you may right-click the sheet and select View Code from the pop-up menu.
- After that, select Module from the Insert drop-down menu.
- Then copy and paste the following VBA code.
VBA Code:
Sub Add_Sheets_with_Variable_Name5()
Sheets.Add(Before:=Sheets(1)).Name = "InitialSheet"
End Sub- Finally, run the code by pressing F5 on your keyboard and you see the result in your worksheet.
- A sheet name InitialSheet will be added in the workbook.
Read More: How to Create New Sheet from Template Using Macro in Excel
Things to Keep in Mind
While using Excel VBA code on your worksheet, make sure you save the file with Excel Macro-Enabled Workbook and the extension will be .xlsm.
Conclusion
The above examples will assist you to Add Sheet with Variable Name in Excel. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback. Or you can glance at our other articles in the ExcelDemy.com blog!
