VBA - adjust to not delete all sheets

[JohnDouglas]

Hi guys

i have hte following line of code that deletes all sheets that aren't named "Main".

   For I = ActiveWorkbook.Worksheets.Count To 1 Step -1
      On Error Resume Next
      If Worksheets(I).Name <> "Main" Then _
         Worksheets(I).Delete
On Error Resume Next
   Next I

But, i'd like a couple of other sheets to not be deleted too. such as "Main 2" etc. Is it possible to adjust this code so that it won't delete any sheet named in an array or any sheet that contains "Main" in its name?

Thanks for your help

John

 

[Andrew Poulsom]

Like this?

If Left(Worksheets(I).Name, 4) <> "Main" Then

 

[pgc01]

Hi John

If you mean that those names start by Main, use

If Left(Worksheets(I).Name,4) <> "Main" Then _

HTH
PGC

 

[Norie]

John

You could test for Main being in the name like this.

For Each ws In Worksheets
     If InStr(ws.Name, "Main")= 0 Then ws.Delete
Next ws

If you want to not delete other sheets you might want to look at a Select Case structure.

For Each ws In Worksheets
     Select Case ws.Name
         Case "Main", "Main 2", "Some other sheet"
               ' do nothing
         Case
               ' ws.Delete
     End Select
Next ws

 

[JohnDouglas]

Fantastic, what a neat idea. Thanks for your swift help.

Cheers

john

 

[JohnDouglas]

norie - thanks, i'll try that out now as well.

 

[JohnDouglas]

norie - how does that code work?

i've got it to work, just odn't understand how it's doing it!

For Each ws In Worksheets
     Select Case ws.Name
         Case "Main", "Main 2", "Some other sheet"
               ' do nothing
         Case ws.Delete
     End Select
Next ws

 

[Norie]

John

There's actually some typos in it, I just typed it here not in the VBE.

Bad habit.

Should be this really.

For Each ws In Worksheets
     Select Case ws.Name
         Case "Main", "Main 2", "Some other sheet"
               ' do nothing
         Case Else
                ws.Delete
     End Select
Next ws

What it does is loop through the worksheets, checks the names of the sheets against the first Case and if the name matches does nothing, otherwise it deletes the sheet.

 

[JohnDouglas]

aha, thanks. that makes more sense with the else in there.

cheers

john