Wasim Akram
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”.
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.
- 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
- In conclusion, you will see we have successfully created a new sheet and renamed it according to our choice.
Read More: Macro to Create New Sheet and Copy Data in Excel (4 Examples)
Similar Readings
- Excel VBA: Add Sheet After Last (3 Ideal Examples)
- How to Add Sheet with Name from Cell Using Excel VBA
- Excel VBA to Add Sheet If It Does Not Exist (with Quick Steps)
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
- 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.
- 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
- 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
- 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.
- Finally, we will have a sheet renamed in our hands.
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.
