macro
 

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

macro
 

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.
--
Gary''s Student - gsnu200823


"EricBB" wrote:

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

macro
 

One mo

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




EricBB wrote:

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

--

Dave Peterson