Excel VBA to Add Sheet with Variable Name (5 Ideal Examples) 

Get FREE Advanced Excel Exercises with Solutions!

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 to add sheet with variable name.


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.

excel vba add sheet with variable name


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.

excel vba add sheet with variable name

  • 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.

excel vba add sheet with variable name

  • This will appear in the Visual Basic Editor where we write our codes.
  • Thirdly, click on Module from the Insert drop-down menu bar.

excel vba add sheet with variable name

  • 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 Run button or pressing the keyboard shortcut F5.

excel vba add sheet with variable name

  • This will add a sheet with the variable name List2.

Note: You don’t need to change the code. All you can do is just change the range as per your requirements.

Read More: How to Add Sheet with Name in Excel VBA


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 button to run the code.

excel vba add sheet with variable name

  • And, finally, you will be able to add a sheet with a variable name List3.


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.

excel vba add sheet with variable name

  • Lastly, you will be able to add a sheet with the variable name List1.

Read More: Excel Macro: Create New Sheet and Rename


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.

excel vba add sheet with variable name

  • That will add a sheet and the sheet variable name will be the name which is in cell C5 and the name is Skin Care.


5. Apply VBA to Add Sheet with Variable Name at Beginning

Now, take a look at another Excel VBA method to add a sheet with variable name at beginning.

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.

excel vba add sheet with variable name

  • A sheet named InitialSheet will be added to the workbook.

excel vba add sheet with variable name

Read More: Excel VBA: Add Sheet After Last


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.


Download Practice Workbook

You can download the workbook and practice with them.


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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sabrina Ayon
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo