not delete worksheets from names in a range

Is there anyway to delete worksheets where one worksheet contains a
range with the names of worksheets I DONT want to delete?
i.e I want to have a code that says "do not delete worksheets in the range
but delete all other worksheets"

You can create a subroutine in vba like this:

Sub delete()
'Tell Excel to not prompt the deletion confirmation
Application.DisplayAlerts = False

For Each sh In ThisWorkbook.Sheets
forDeletion = True

' Sheet1.Range("A1:A3") refers to the range of cells that
' hold the sheet needs we don't want to delete. You're better
' off using a named range to refer to those.
For Each n In Sheet1.Range("A1:A3")
If sh.Name = n.Value Then
forDeletion = False
Exit For
End If
Next n
If forDeletion Then
sh.delete
End If
Next sh
End Sub




"DARREN FONG" wrote:

Is there anyway to delete worksheets where one worksheet contains a
range with the names of worksheets I DONT want to delete?
i.e I want to have a code that says "do not delete worksheets in the range
but delete all other worksheets"

One way:

Sub DelShts()
Dim rng() As Variant
Dim i As Integer
Dim ans As Variant
Application.DisplayAlerts = False
rng = Range("A2:A6")
For i = Sheets.Count To 1 Step -1
On Error Resume Next
ans = Application.WorksheetFunction. _
Match(Sheets(i).Name, rng, 0)
If ans = Empty Then Sheets(i).Delete
ans = Empty
On Error GoTo 0
Next i
Application.DisplayAlerts = True
End Sub

Hope this helps
Rowan

DARREN FONG wrote:
Is there anyway to delete worksheets where one worksheet contains a
range with the names of worksheets I DONT want to delete?
i.e I want to have a code that says "do not delete worksheets in the range
but delete all other worksheets"

Jason,

Thanks for your help, however it does'nt seem to work

I think this is because "n" is not defined, I've tried a few things but
nothing works as yet

Cheers

Daz

Jason Clement wrote:
You can create a subroutine in vba like this:

Sub delete()
'Tell Excel to not prompt the deletion confirmation
Application.DisplayAlerts = False

For Each sh In ThisWorkbook.Sheets
forDeletion = True

' Sheet1.Range("A1:A3") refers to the range of cells that
' hold the sheet needs we don't want to delete. You're better
' off using a named range to refer to those.
For Each n In Sheet1.Range("A1:A3")
If sh.Name = n.Value Then
forDeletion = False
Exit For
End If
Next n
If forDeletion Then
sh.delete
End If
Next sh
End Sub

Is there anyway to delete worksheets where one worksheet contains a
range with the names of worksheets I DONT want to delete?
i.e I want to have a code that says "do not delete worksheets in the range
but delete all other worksheets"

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200511/1