Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |