[RESOLVED] Need to delete some worksheets and save others

I am new to VB, but fortunately you guys/gals have been extremely helpful.

I am working on a macro for excel. When played in a workbook I want the macro to ignore three worksheets that I am saving “MM”, “JE”, and “JO” but delete the rest. The worksheets that need to be deleted vary in amount and name.

If the worksheets that you are wishing to save never change then this can be achieved by looping around the sheets collection and deleting sheets that do not match criteria.

Two ways..

Example 1 - Deleting as you go

VB Code:

Dim i As Integer
  DisplayAlerts = False
LoopReEnter:
  For i = 1 to Sheets.Count
    If Sheets(i).Name <> "MM" Or Sheets(i).Name <> "JE" Or Sheets(i).Name <> "JO" Then
      Sheets(i).Delete
      Goto LoopReEnter
    End If
  Next i
  DisplayAlerts = True

Example 2 - Selecting then Deleting

VB Code:

Dim i As Integer
  DisplayAlerts = False
  For i = 1 to Sheets.Count
    If Sheets(i).Name <> "MM" Or Sheets(i).Name <> "JE" Or Sheets(i).Name <> "JO" Then
      Sheets(i).Select
    End If
  Next i
  Selection.Delete
  DisplayAlerts = True

In your macro code do a test for the sheet name and if its one of the three then skip deleting.

VB Code:

Dim i As Integer
Application.DisplayAlerts = False
For i = 1 To Sheets.Count
    If Sheets(i).Name = "MM" Or Sheets(i).Name = "JE" Or Sheets(i).Name = "JO" Then
        'Skip
    Else
        Sheets(i).Delete
    End If
Next
Application.DisplayAlerts = True

Me Win.. with more..

Its easier to iterate backwards through your sheets this way you won't get into trouble due to reindexing sheets in a sheet deletion. ( the sheet after the sheet you delete will get the index of the deleted sheet)
I hope it makes sence to you :)

Code:

---

Dim i As Integer
DisplayAlerts = False

  For i = Sheets.Count to 1 step -1
    If Sheets(i).Name <> "MM" Or Sheets(i).Name <> "JE" Or Sheets(i).Name <> "JO" Then
      Sheets(i).Delete
          End If
  Next i
DisplayAlerts = True

---

Anymore for anymore?? :D
BTW Dnereb.. you following me?? and I also covered that off in my first example.. notice the goto statement :D

Yes, I forgot the reverse looping. Oh well. Easy enough to take care of though.

You can "win" Danny although it not a competition. Its just helping out members with their issues. ;)

It could be a problem on my end but,

Danny, I played with both of your sets of code, the first deletes all of the worksheets, the second deletes a cell if a cell is selected then moves to the end worksheet and stops.

RobDog, your code does save and delete the correct worksheets, but goes into debug mode depending on the position of the saved and deleted worksheets. Also, what code did you have in mind for skipping?

Dnereb like Danny your code deletes all of the worksheets.

Sorry second example should be..

VB Code:

Dim i As Integer
  DisplayAlerts = False
  For i = 1 to Sheets.Count
    If Sheets(i).Name <> "MM" Or Sheets(i).Name <> "JE" Or Sheets(i).Name <> "JO" Then
      Sheets(i).Select
    End If
  Next i
  Selection.Delete
  ActiveWindow.SelectedSheets.Delete
  DisplayAlerts = True

My First example should of worked fine as it is just the reverse of RD's, it should only delete the sheet if the name does not match..

RD

I know just kidding..

Yes, as I mentioned in my last post. You need to reverse the loop by...

VB Code:

For i = Sheets.Count To 1 Step -1

Ps, Danny, I know you are but others may not. ;)

Thanks for the quick reply, but this time it deleted the end sheet which happened to be "JE" then stopped. Perhaps it is something on my end; but I have tripled checked the names, and the sheets to be deleted are just inserted sheets that I am using to hammer on.

I reversed Rob Dog's loop and now it works like a charm. Thank much!

Post the code your using so we are all on the same page. ;)

VB Code:

Dim i As Integer
Application.DisplayAlerts = False
For i = Sheets.Count To 1 Step -1
    If Sheets(i).Name = "MM" Or Sheets(i).Name = "JE" Or Sheets(i).Name = "JO" Then
        'Skip
    Else
        Sheets(i).Delete
    End If
Next
Application.DisplayAlerts = True
     
    Set ws = Nothing