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

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.

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 to create a table from range.
  • 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 RubSub button or pressing the keyboard shortcut F5.

excel vba add sheet with variable name

  • This will add 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 (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.

excel vba add sheet with variable name

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

excel vba add sheet with variable name

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

excel vba add sheet with variable name

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

excel vba add sheet with variable name

  • A sheet name InitialSheet will be added in the workbook.

excel vba add sheet with variable name

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!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo