Delete worksheet in Excel using VBA

Question

I have a macros that generates a number of workbooks  I would like the macros  at the start of the run  to check if the file contains 2 spreadsheets  and delete them if they exist   The code I tried was   If Sheet Name    ID Sheet  Then     Application DisplayAlerts   False     Sheet Delete     Application DisplayAlerts   True  End If  If Sheet Name    Summary  Then     Application DisplayAlerts   False     Sheet Delete     Application DisplayAlerts   True End If   This code is returning an error      run time error  424  object required    I probably have the wrong formatting  but if there is an easier way to do this  it would be very useful

User · Accepted Answer

Consider   Sub SheetKiller       Dim s As Worksheet  t As String     Dim i As Long  K As Long     K   Sheets Count      For i   K To 1 Step -1         t   Sheets i  Name         If t    ID Sheet  Or t    Summary  Then             Application DisplayAlerts   False                 Sheets i  Delete             Application DisplayAlerts   True         End If     Next i End Sub   NOTE   Because we are deleting  we run the loop backwards

User · Answer

You could use On Error Resume Next then there is no need to loop through all the sheets in the workbook    With On Error Resume Next the errors are not propagated  but are suppressed instead  So here when the sheets does t exist or when for any reason can t be deleted  nothing happens  It is like when you would say   delete this sheets  and if it fails I don t care  Excel is supposed to find the sheet  you will not do any searching    Note  When the workbook would contain only those two sheets  then only the first sheet will be deleted   Dim book Dim sht as Worksheet  set book  Workbooks  SomeBook xlsx    On Error Resume Next  Application DisplayAlerts False   Set sht   book Worksheets  ID Sheet   sht Delete  Set sht   book Worksheets  Summary   sht Delete  Application DisplayAlerts True   On Error GoTo 0

User · Answer

Worksheets  Sheet1   Delete Worksheets  Sheet2   Delete

User · Answer

Try this code   For Each aSheet In Worksheets      Select Case aSheet Name          Case  ID Sheet    Summary              Application DisplayAlerts   False             aSheet Delete             Application DisplayAlerts   True      End Select  Next aSheet

User · Answer

try this within your if statements   Application DisplayAlerts   False Worksheets    Sheetname     Delete Application DisplayAlerts   True

[vba] Delete worksheet in Excel using VBA

Examples related to vba

Examples related to excel