Excel VBA is a great tool for manipulating everything that is in your sheets. It is also a great way to manipulate the sheets themselves, and workbooks as well.
In the example below, we will show how to add a sheet at the end of our sheets list.
Add Sheet at the End of All Sheets in VBA
For our example, we will presume that we have three sheets in our workbook, which are called simply by their generic names: Sheet1, Sheet2, and Sheet3:
Now, we can simply click on the plus sign to add another sheet. However, if we would go to Sheet2 and then click on the plus sign, it would automatically add a sheet between Sheet2 and Sheet3, so not on the end of our list, where we want it.
For this and many more reasons, we can use VBA to automatically create the sheet at the end. To insert our code, we need to go to the VBA editor. To do so, we press ALT + F11 on our keyboard. After that, on the window that appears, we will right-click on the left window and choose Insert >> Module:
Once clicked, we will be presented with the clear text field, in which we will insert the following code:
|
1 2 3 |
Sub AddSheetsAtTheEnd() Sheets.Add After:=Sheets(Sheets.Count) End Sub |
This is what our code looks like in the editor:
It is one line of code. Once you type in “Sheets.Add” and press space, you will be presented with the options for adding your sheet:
You can see that we can add sheets before or after a certain object. We can also count the number of objects and then add ours. For our example, we use the “After” option, and then we count the total number of already existing sheets. This guarantees that the added sheet is always in the last place. We will add one more line of code, right above the End Sub:
|
1 |
ActiveSheet.Name = "Latest Sheet" |
We know that the created sheet will be the one active, so we can use the line above to give this sheet a proper name.
Once we execute our code, this is the result we get:
Now, if we would try to execute the same code again, this is the message we would get:
Excel warns us that we cannot have two sheets with the same name. Our sheet will, however, be added. We will delete this sheet. Of course, we did not have to add the last line of code and give the name to our new sheet. When we delete this line of code, we will get a new sheet with a generic name:
