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

VBA macro is one of the most remarkable features in Excel. Using VBA code you can do any type of work without any hesitation. If you are eager to learn, start now with this link. 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 new sheet and rename using excel macro.


Download Practice Workbook

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


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 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: Macro to Create New Sheet and Copy Data in Excel (4 Examples)


Similar Readings


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 it monthwise.

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


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: How to Add Sheet with Name in Excel VBA (6 Easy Ways)


Things to Remember

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

Conclusion

In this article, I have tried to cover all the methods to create 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. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo