Ok what did i do wrong....

[shadow12345]

I try this time, but I still can't grasp this. I am trying to say if the worksheet name doesn't equal one of the names in the array then delete it.


This is what I tried but it doesn't work.

Sub delete_sheets()

ws = Worksheet
wb = Workbook

For Each ws In wb
If ws.Name <> Array("Control Sheet", "Source Data", "Apim") Then Delete.ws
Next ws


End Sub

Can anyone help? I would prefer to know how to fix mine if possible so I can at least undestand a bit more.

 

[Juan Pablo González]

You can't compare a string (ws.Name) with an array.

You could use something like this

Select Case ws.Name
Case "Control Sheet", "Source Data", "Apim"
   'Do nothing
Case Else
   ws.Delete
End Select

 

[shadow12345]

oh ok, case... Ill go try that one.

(I know you have writen it our for me but i need to learn so ill give it a go without looking at yours.)

 

[shadow12345]

Hmmmm, i don't see any difference between what you did and what I did... but mine doens't work. It say Object required at the select case line.

Sub delete_sheets()

ws = Worksheet
wb = Workbook

Select Case ws.Name
Case "Control Sheet", "Source Data", "Apim"
' do not delete
Case Else
ws.Delete
End Select

End Sub

 

[MrKowz]

There are a few things wrong with your code:

1. Your variables must be Dimmed. You can't just state ws = worksheet. It needs to be Dim ws as Worksheet.
2. Your For Each statement needs to be For Each ws In ActiveWorkbook.Worksheets
3. You cannot directly compare a singular value (ws.name) versus an array. If you want to see if that value is IN the array, you can use Application.Lookup.
4. Delete.ws is not a valid command. The object must go before the method. ws.Delete would be proper.

Try the following:

Sub delete_sheets()
Dim ws As Worksheet
Dim x  As Variant
For Each ws In ActiveWorkbook.Worksheets
    x = Application.Lookup(ws.Name, Array("Control Sheet", "Source Data", "Apim"))
    If Not IsError(x) Then ws.Delete
Next ws

End Sub

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG02Mar20
[COLOR="Navy"]Dim[/COLOR] ws [COLOR="Navy"]As[/COLOR] Worksheet
[COLOR="Navy"]Dim[/COLOR] nam [COLOR="Navy"]As[/COLOR] Variant
Application.DisplayAlerts = False
[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] ws [COLOR="Navy"]In[/COLOR] Worksheets
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] nam [COLOR="Navy"]In[/COLOR] Array("Control Sheet", "Source Data", "Apim")
        [COLOR="Navy"]If[/COLOR] ws.Name = nam [COLOR="Navy"]Then[/COLOR]
            ws.Delete
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] nam
[COLOR="Navy"]Next[/COLOR] ws
Application.DisplayAlerts = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Norie]

If you want to check the names of all the worksheets you need to loop through them.

Sub delete_sheets()
Dim wb As Worksheet
Dim ws As Worksheet
 
    Set wb = ThisWorkbook ' set reference to the workbook you want  to check
 
    For Each ws In wb.Worksheets

              Select Case ws.Name
                    Case "Control Sheet", "Source Data", "Apim"
                              ' do not delete
              Case Else
                     ws.Delete
          End Select 
 
     Next ws
End Sub

 

[shadow12345]

I got it.... change the wb to workbook and it works!

Sub delete_sheets()

Dim wb As workbook

Dim ws As Worksheet
 
    Set wb = ThisWorkbook ' set reference to the workbook you want  to check
 
    For Each ws In wb.Worksheets

              Select Case ws.Name
                    Case "Control Sheet", "Source Data", "Apim"
                              ' do not delete
              Case Else
                     ws.Delete
          End Select 
 
     Next ws
End Sub

 

[Norie]

That was a deliberate mistake obviously.