Skip to content

Dear Internet Explorer user: Your browser is no longer supported

Please switch to a modern browser such as Microsoft Edge, Mozilla Firefox or Google Chrome to view this website's content.

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
End Sub

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

Excel dialogue box
The following message will pop-up if Application.DisplayAlerts = true: “Data may exist in the sheet(s) selected for deletion. To permanently delete the date, press Delete.”

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



No comments have yet been submitted. Be the first!

Have Your Say

The following HTML is permitted:
<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>

Comments will be published subject to the Editorial Policy.