macro

E

EricBB

I have an Array("1","2","3") which correspond with the workbook sheet names.

how can I delete the sheets which is not in array list using VBA? I want
only to retain the sheets which is in the array list.

thanks
 
G

Gary''s Student

Sub sheet_killer()
ssave = Array("1", "2", "3")
For Each sh In Sheets
n = sh.Name
killit = True
For i = 0 To 2
If n = ssave(i) Then
killit = False
End If
Next
If killit Then
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
End If
Next
End Sub

So if you have sheets named:
Sheet1
Sheet2
Sheet5
1
2
3

then Sheet1, Sheet2, Sheet5 will be deleted.
 
D

Dave Peterson

One more:

Option Explicit
Sub testme()
Dim myNames As Variant
Dim res As Variant
Dim sh As Object

myNames = Array("1", "2", "3")

For Each sh In ActiveWorkbook.Sheets
res = Application.Match(sh.Name, myNames, 0)
If IsError(res) Then
'not in array, so delete it
Application.DisplayAlerts = False
On Error Resume Next
sh.Delete
If Err.Number <> 0 Then
Err.Clear
MsgBox sh.Name & " not deleted"
End If
On Error GoTo 0
Application.DisplayAlerts = True
End If
Next sh
End Sub
 
Top