Excel Macro Create New Sheet Tutorial

posted in: Excel Tutorials | 0

Excel Macro Create New Sheet Tutorial

excel tutorials for dummies

Create new Sheet VBA Tutorial

In this guide we will outline how you can use Excel VBA to create a new sheet. This is handy if you have the need to quickly create a lot of new sheets in Excel, or when built in alongside other macro code for a whole host of possibilities. In our example we use to outline how this is executed, we simply use Excel VBA to add a new worksheet and also have that worksheet selected. This relies on  a small amount of VBA code that we will term Create New Sheet VBA.

Excel Macro Create New Sheet – Getting Started

If you are a beginner to Microsoft Excel macros and working with VBA code you will need to have followed our beginner’s guide which will get you up and running to be able to create a new sheet with VBA.

In our working example, our macro button (renamed to add sheet for clarity), simply adds a new sheet onto the end of the sheets that are already present. It also selects that sheet. For instance in our example we are up to Sheet5, when we run the macro it will create and select Sheet6.


excel vba create new sheet




VBA new sheet

To get VBA to add a new sheet is a very simple process and the code can be found below. This should not need much altering for your own needs.


Create new sheet VBA code

Private Sub CommandButton1_Click()

ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)

End Sub

If you need to VBA to add a sheet to appear at the start of the sheet index, ie in front of where Sheet1 is located at the bottom left of the screen, this is possible. This requires a minor alteration to the Excel VBA add worksheet code. To do this in the code supplied above you will need to change the word “after” for the word “before”.

Create new Sheet VBA video tutorial

This concludes our Excel VBA create sheet guide. If this has been helpful then please like or subscribe to the YouTube channel for more content.

Leave a Reply