VBA - adjust to not delete all sheets

JohnDouglas

Board Regular
Joined
Jan 5, 2005
Messages
239
Hi guys

i have hte following line of code that deletes all sheets that aren't named "Main".
Code:
   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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
Hi John

If you mean that those names start by Main, use

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

HTH
PGC
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,351
Office Version
  1. 365
Platform
  1. Windows
John

You could test for Main being in the name like this.
Code:
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.
Code:
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

Board Regular
Joined
Jan 5, 2005
Messages
239
Fantastic, what a neat idea. Thanks for your swift help.

Cheers

john
 

JohnDouglas

Board Regular
Joined
Jan 5, 2005
Messages
239
norie - how does that code work?

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

Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,351
Office Version
  1. 365
Platform
  1. Windows
John

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

Bad habit. o_O

Should be this really.
Code:
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.
 

Forum statistics

Threads
1,171,203
Messages
5,874,332
Members
433,044
Latest member
drewbizzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top