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.