Delete Worksheet in Excel VBA

Home/Excel VBA/Delete Worksheet in Excel VBA

VBA delete worksheet Excel Macro Example Code helps to delete Worksheet in Exce while automating some kind of requests. For example, We may have the request to collate the data from different worksheets of the workbook. We do automate it and finally we we delete the unnecessary worksheets from the workbook.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Ultimate Project Management Template
Excel Template

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


VBA Delete Worksheet in Excel – Solution(s):

Delete Worksheet in Excel VBAWe can delete a Worksheet from a Workbook using Delete Method of the Worksheet in Excel VBA. The following examples will show you how to delete a Worksheet form Workbook.

Delete Worksheet in Excel VBA – An Example:

In this example, I am deleting a worksheet (named Sheet2).

Code:
Sub sbDeleteASheet()

Sheet1.Delete
'OR You can mention the Sheet name
Sheets("Sheet2").Delete

End Sub
Instructions:
  1. Open an excel workbook
  2. Insert 2 Worksheets and enter some data
  3. Press Alt+F11 to open VBA Editor
  4. Insert new module from the Insert Menu
  5. Copy the above code and Paste in the code window
  6. Save the file as macro enabled workbook
  7. Press F5 to execute the code.
Output:

When you execute the above code, it will show you a warning message called application alerts. If you click on delete button it will delete the Worksheet.
Delete worksheets Examples - application alert

Delete Worksheet in Excel VBA – Disable Application Alerts or Warning message:

While executing the above code, we got a warning message. If you want to avoid that warning message, you need to disable the application alerts. The following example will show you how to delete a Worksheet and stop showing warning messages.

Code:
Sub sbDeleteASheet()

'Stopping Application Alerts
Application.DisplayAlerts=FALSE

Sheet1.Delete
'OR You can mention the Sheet name
Sheets("Sheet2").Delete

'Enabling Application alerts once we are done with our task
Application.DisplayAlerts=TRUE

End Sub
Instructions:
  1. Open an excel workbook
  2. Insert 2 Worksheets and enter some data
  3. Press Alt+F11 to open VBA Editor
  4. Insert new module from the Insert Menu
  5. Copy the above code and Paste in the code window
  6. Save the file as macro enabled workbook
  7. Press F5 to execute the code.
Output:

If you execute the code, now you will not see any alerts. Since we have disabled the application alerts.


 
Related Resource External VBA Reference
Excel VBA Reference Project Management Reference
VBA Reference:
Excel Reference:
By |January 27th, 2013|Excel VBA|6 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

6 Comments

  1. Chris Small January 19, 2014 at 8:22 PM - Reply

    For the code above under ‘Delete Worksheet in Excel VBA – Disable Application Alerts or Warning message:’, the last line of code before end sub is ‘Application.DisplayAlerts=FALSE’. Shouldn’t this be ‘Application.DisplayAlerts=TRUE’?

    • PNRao January 19, 2014 at 8:26 PM - Reply

      Thanks Chris,

      It should be Application.DisplayAlerts=TRUE, I changed it.

      Thanks-PNRao!

  2. Celia February 26, 2016 at 3:19 AM - Reply

    I have a workbook with over 500 worksheets of which we only need 100. I know the names of the worksheets that need to be deleted but I am not sure where in the code I would add them. Your help is much appreciated! Thanks

    • Raj August 29, 2016 at 7:58 AM - Reply

      Sub Deleting_Unrequired_worksheets()
      Application.DisplayAlerts = False
      Sheets(“Report Title”).Delete
      Sheets(“Reject Details”).Delete
      Sheets(“Query Details”).Delete
      Sheets(“Manual Err Details”).Delete
      Sheets(“Samplers Accuracy”).Delete
      Sheets(“Samplers Details”).Delete
      Sheets(“My Workflow”).Delete
      Application.DisplayAlerts = True
      End Sub

  3. s January 30, 2017 at 8:29 PM - Reply

    it’s good for me
    thanks for sharing

  4. elghi January 13, 2018 at 11:23 AM - Reply

    and how to delete multiple sheets in vba ? help me please

Leave A Comment

This site uses Akismet to reduce spam. .


Related pages


how to do data validation in excel 2007advanced pivot table in excel 2007vlookup definition wikipediavba open access database from excelmultiple project timeline template excelcheckbox in powerpointvba hide columnsexcel vba printget file name vbahow to lock excel sheetexcel vba create checkboxfind duplicate values excelfind duplicate rows in excelvb6 check if file existspivotal table in excelvba servervba sleephow do i unlock an excel filevba for excel tutorial for beginnerswhat is the lookup function in excelexcel averageifssurface charts in excelvba cells functionvba connection string to access databaselistbox with columnsdynamic pivot tablescopy worksheet vbaquery sql from excelexcell concatenatevba adodb.connectionhow to use iferror with vlookupvba current directoryvba excel file existshow to password protect excel sheetsql queries asked in interviews pdfexcel copy row to another sheetexcel transfer data between worksheetsfreshers interview questions and answers pdfconsolidate multiple excel sheets into onevbscript to delete filesmove worksheet to another workbookopen file dialog vbvba excel hyperlinkexcel vba protectfso create folderexcel reverse vlookupexcel remove blankexcel sort macropivot table tutorialsspecialcells vbavbscript tutorial for beginners step by stepms sql interview questions and answers pdfhide a column in excelcreate excel macrosexcel array tutorialwhere is the developer tab in excel 2013excel vba copy range to another worksheetexcel 2007 offsetcharttype vbavba programming languageinterior vbavba to open excel file from foldervbscript examples programshyperlink vbavba input message boxhow to create pivot table in excel 2007template dashboard excelhow to delete odd rows in excelconsolidate spreadsheetsremoving duplicates in excelexcel vba database examplesvba vbokcancelconvert excel file to xmlms excel countif functionvba excel tutorialsumif in vbahow to unhide row in excelmost useful programsexcel formulas matchexcel vba sample