Md. Abdur Rahim Rasel
Sometimes, you might add an Excel sheet after the last for the convenience of your work. Adding an Excel sheet after the last is an easy task. This is a time-saving task also. In this article, we’ll learn three quick and suitable ways how Excel VBA adds sheet after last effectively with appropriate illustrations.
Excel VBA to Add Sheet After Last (Quick View)
Sub Add_Sheet_After_Last()
Sheets.Add After:=Sheets(Sheets.Count)
End Sub
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Suitable Examples to Add Sheet After Last Using VBA in Excel
In the following figure, you see that we have 3 sheets which are mainly salaries in months along a year. Also, the names of the sheets are like Salary_Jan, Salary_Feb, and Salary_Mar. We will add the sheet after the last sheet using a simple VBA code. Here’s an overview of the dataset for today’s task.
1. Excel VBA to Add Sheet After Last Sheet in Workbook
Now I’ll show how to add an Excel sheet after the last sheet by using a simple VBA code. It’s very helpful for some particular moments. From our dataset, we will add an Excel sheet after the last sheet. Let’s follow the instructions below to add an Excel sheet using VBA code!
Step 1:
- First of all, open a Module, to do that, firstly, from your Developer tab, go to,
Developer → Visual Basic
- After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Add Sheet After Last will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,
Insert → Module
Step 2:
- Hence, the Add Sheet After Last module pops up. In the Add Sheet After Last module, write down the below VBA code.
Sub Add_Sheet_After_Last()
Sheets.Add After:=Sheets(Sheets.Count)
End Sub
- Hence, run the VBA To do that, go to,
Run → Run Sub/UserForm
- After running the VAB Code, you will be able to add an Excel sheet after last which has been given in the below screenshot.
Read More: How to Add Sheet with Name from Cell Using Excel VBA
2. Insert Sheet After Last with Name
In this method, we will add a sheet with the name in the Excel workbook using VBA code. From our dataset, we will add an Excel sheet after the last sheet with the name. Let’s follow the instructions below to learn!
Step 1:
- First, according to method 1, insert a new module and type the below VBA code to add an Excel sheet with the name. The VBA code is,
Sub Add_Sheet_with_Name()
Sheets.Add.Name = "Salary_Apr"
End Sub
- Further, run the VBA To do that, go to,
Run → Run Sub/UserForm
Step 2:
- After running the VAB Code, you will be able to add an Excel sheet after last named “Salary_Apr” which has been given in the below screenshot.
Read More: Excel Macro: Create New Sheet and Rename (3 Ideal Examples)
3. Excel VBA to Add Sheet After Specific Sheet
Last but not the least, we will add an Excel sheet after a specific sheet by using a simple VBA code. From our dataset, we will add an Excel sheet after the Salary_Feb sheet. This is an easy task and time-saving also. Let’s follow the instructions below to add an Excel sheet after a specific sheet!
Step 1:
- First, according to method 1, insert a new module to add an Excel sheet after a specific sheet named Salary_Feb and type the below VBA code to add an Excel sheet with the name. The VBA code is,
Sub Add_Sheet_After_Specific_Sheet()
Sheets.Add After:=Worksheets("Salary_Feb")
End Sub
- Further, run the VBA To do that, go to,
Run → Run Sub/UserForm
Step 2:
- After running the VAB Code, you will be able to add an Excel sheet after the specific named “Salary_Feb” which has been given in the below screenshot.
Read More: Excel VBA to Add Sheet If It Does Not Exist (with Quick Steps)
Excel VBA to Add Sheet at the Beginning in Workbook
Now, we will add an Excel sheet at the beginning of our workbook using a simple VBA code. Let’s follow the instructions below to learn!
Step 1:
- First, according to method 1, insert a new module and type the below VBA code to add a sheet at the beginning of our workbook. The VBA code is,
Sub Add_Sheet_Beginning()
Sheets.Add Before:=Sheets(1)
End Sub
- Further, run the VBA To do that, go to,
Run → Run Sub/UserForm
Step 2:
- After running the VAB Code, you will be able to add an Excel sheet at the beginning of our workbook which has been given in the below screenshot.
Read More: Excel VBA to Add Sheet with Variable Name (5 Ideal Examples)
Things to Remember
👉 You can pop up Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.
👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,
File → Option → Customize Ribbon
Conclusion
I hope all the suitable methods mentioned above to add sheet after last with VBA code will provoke you to apply them in your Excel spreadsheets with more productivity. You can also visit our website ExcelDemy to learn more Excel-related content. You are most welcome to feel free to comment if you have any questions or queries.
