Solved Code to delete specific sheets in a workbook

October 21, 2013 at 12:48:12
Specs: N/A
I need to delete specific sheets in a workbook and run the rest of the code even if the sheets don't exist

See More: Code to delete specific sheets in a workbook

Report •

October 21, 2013 at 19:45:14
Are you asking for help or just telling us what you need?

If you are asking for help you need to do 2 things:

1 - Actually ask a question.
2 - Supply some more details other than just "specific sheets".

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

October 22, 2013 at 10:04:44
'Thank You, I have a macro(see below) that i'm running to delete 3 sheets in a workbook. The 3 sheets contain examples and instructions and are in a Template and once the template has been filled out are no longer needed I want to delete the sheets and if the sheets have already been deleted to ignore this step and run the rest of the macro.

Dim ws As Worksheet
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Application.DisplayAlerts = False
On Error Resume Next
Set ws = Sheets("Instructions")
Set ws1 = Sheets("Example DEF")
Set ws2 = Sheets("Assembly status")
If Err <> 0 Then Exit Sub

Application.DisplayAlerts = True

ActiveSheet.Hyperlinks.Add Anchor:=Range("$E$10"), Address:=Range("$E$10").Value, TextToDisplay:=Range("$E$10").Value
ActiveSheet.Hyperlinks.Add Anchor:=Range("$E$11"), Address:=Range("$E$11").Value, TextToDisplay:=Range("$E$11").Value
ActiveWindow.View = xlPageLayoutView
thisfile = Range("Macro_data!$A$1").Value
ActiveWorkbook.SaveAs FileName:=thisfile

thank you for the help.

Report •

October 22, 2013 at 12:54:30
✔ Best Answer
First, a posting tip:

Before posting any more VBA code in this forum, please click on the blue line at the end of this post and read the instructions on how to post code and data in this forum.

As for your question, one way to accomplish your goal is to loop through the sheets and delete the ones with the names you are looking for.

You'll note that we loop through the sheets in reverse order because deleting sheets while counting forward messes up the For-Next loop. e.g. If Sheets.Count = 6 at the start of a forward counting loop and then a sheet gets deleted, there will only be 5 sheets left. The code would then fail when it tries to access Sheet(6). By counting in reverse order, we eliminate that issue.

Sub DelSheets()
 For sht = Sheets.Count To 1 Step -1
  If _
     Sheets(sht).Name = "Instructions" Or _
     Sheets(sht).Name = "Example DEF" Or _
     Sheets(sht).Name = "Assembly status" Then
       Application.DisplayAlerts = False
       Application.DisplayAlerts = True
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

Ask Question