Automate the creation or deletion of worksheets in Excel

Use VBA to create or delete worksheets in Microsoft Excel.

You can easily create or delete worksheets in Microsoft Excel with a Visual Basic for Applications (VBA) script.

Let’s assume that you wish to make a new worksheet and call it “Profit-Statement”. The following script, called ‘CreateSheet’, will perform that function and place the sheet after the first worksheet in Excel.

Sub CreateSheet()
Worksheets.Add(After:=Worksheets(1)).Name = "Profit-Statement"
End Sub

Conversely, the opposite can be achieved with a script that I will call ‘DeleteSheet’. This script identifies the “ProfitStatement” sheet by name and deletes it without warning:

Sub DeleteSheet()
    Application.DisplayAlerts = False 'This line prevents a warning dialog box from popping up
    Sheets("Profit-Statement").Delete
End Sub

If you change Application.DisplayAlerts = True, the following warning will pop-up in Excel when the script attempts to delete the worksheet:

These two scripts can be incorporated into other larger functions that require the creation or deletion of new sheets.

Have Your Say

Δ