Excel Macro: Create New Sheet and Rename (3 Ideal Examples)

Get FREE Advanced Excel Exercises with Solutions!

VBA macro is one of the most remarkable features in Excel. Using VBA code you can do any type of work without any hesitation. While working in Microsoft Excel sometimes you might need to create a new sheet and rename the sheet. Today in this article, I am sharing with you how to create a new sheet and rename using Excel macro.


3 Easy Methods to Create New Sheet and Rename Using Excel Macro

In the following, I have explained 3 easy and quick methods to create a new sheet and rename using Excel macro.

Suppose we have a dataset of “Sales of January” in a worksheet named “January”.

Excel Macro Create New Sheet and Rename

And in another worksheet, we have the data for “Sales of February”.

Now we are going to create new sheet and rename it using the Excel macro. Stay tuned!


1. Create a Single Sheet and Rename Using Excel Macro

In this method, I will show you how to add a single sheet and rename the sheet using the Excel VBA macro. Follow the steps below-

Steps:

  • While the workbook is opened press Alt+F11.
  • Thus, “Microsoft Visual Basic for Applications” will open.

Excel Macro Create New Sheet and Rename

  • Next, create a new “Module” from the “Insert” option.

  • After that, in the new module write the following code and hit the “Run” icon to continue-
Sub Create_New_Sheet_Rename()
Sheets.Add After:=ActiveSheet
Sheets(ActiveSheet.Name).Name = "March"
End Sub

Excel Macro Create New Sheet and Rename

  • In conclusion, you will see we have successfully created a new sheet and renamed it according to our choice.

Excel Macro Create New Sheet and Rename

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


2. Make Multiple Sheets and Rename with Excel Macro

Sometimes you might need to open multiple sheets and rename those sheets. Applying VBA code you can do that easily.

Suppose we have a dataset of a company’s “Total Sales” according to different months in a single sheet. Now we will make multiple sheets and rename them according to the months.

Steps:

  • While in the workbook open the “Microsoft Visual Basic for Applications” by pressing Alt+F11.

  • In the new module put the following code down and press the “Run” icon-
Sub create_sheets()
Dim Sheet As Worksheet
For i = 1 To 8
For Each Sheet In ActiveWorkbook.Worksheets
If Sheet.Name <> Sheet2.Cells(i + 1, 1) Then
a = a
Else
a = a + 1
End If
Next Sheet
If a = 0 Then
Set Sheet = Sheets.Add(after:=Sheets(Sheets.Count))
Sheet.Name = Sheet2.Cells(i + 1, 1)
End If
Next i
End Sub

Excel Macro Create New Sheet and Rename

  • In summary, we will get multiple sheets and rename them monthwise.

Read More: How to Add Sheet with Name from Cell Using Excel VBA


3. Run a Macro Code to Create Sheet First and Then Rename in Excel

In this final method, we will create a sheet first and then rename it with another VBA macro.

Step 1:

  • First, open the workbook and press Alt+F11 to open the “Microsoft Visual Basic For Applications”.

  • Second, choose “Module” from the “Insert” option.

Excel Macro Create New Sheet and Rename

  • Hence, in the module write the following code and press “Run” to run the code-
Sub Sheet_Create_New_Sheet()
ActiveWorkbook.Sheets.Add
End Sub

Excel Macro Create New Sheet and Rename

  • As you can see a new worksheet is created with the macro.

Step 2:

  • If you want you can add multiple sheets too.
  • Similarly, open a module and run the following code-
Sub Create_Multiple_Sheets()
Sheets.Add Count:=3
End Sub

Excel Macro Create New Sheet and Rename

  • As a result, multiple sheets are created in a proper way.

Step 3:

  • This time we will rename the sheet. To do so-
  • Similarly, put the following code in the module-
Sub Rename_Sheet()
Dim X As Worksheet
Set X = Worksheets("Sheet1")
X.Name = "March"
End Sub
  • Press the “Run” icon.

Excel Macro Create New Sheet and Rename

  • Finally, we will have a sheet renamed in our hands.

Excel Macro Create New Sheet and Rename

Read More: Excel VBA to Add Sheet with Variable Name


Things to Remember

  • Before applying the code don’t forget to insert modules for every method. Otherwise, it won’t work.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

In this article, I have tried to cover all the methods to create a new sheet and rename using Excel macro. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. Stay tuned and keep learning.


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.
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo