PDA

View Full Version : [SOLVED] Another Delete Sheets Macro



KongUK
10-17-2017, 01:46 AM
Hi

I have looked for a basic method to do this but cannot seem to find it :-/

I just want to delete any sheet that is in a list on a defined sheet as a list
So list may be 20 sheet names in column A1:20, but it needs to be dynamic so I can amend the list

Code I had working before uses specific sheet name


Dim i As Integer, n As Integer
n = ThisWorkbook.Worksheets.Count


Set MyList = Sheets("Results").Range("A1")
Set MyList = Range(MyRange, MyRange.End(xlDown))


Application.DisplayAlerts = True

For i = n To 1 Step -1


On Error Resume Next
If InStr(1, Sheets(i).Name, "Test Sheet") Then Sheets(i).Delete 'named sheet works
If InStr(1, Sheets(i).Name, MyList) Then Sheets(i).Delete ' Use variable for sheet name?
On Error GoTo 0
Next I



If using line to of the InSTr, it works but deletes all sheets in workbook

Thanks for the help

mancubus
10-17-2017, 02:20 AM
Sub vbax_61039_del_sheets_in_list()

Dim i As Long
Dim MyList

With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With

With Worksheets("Results")
MyList = .Range("A1:A" & .Range("A1").End(xlDown).Row).Value
End With


On Error Resume Next
For i = LBound(MyList, 1) To UBound(MyList, 1)
If Worksheets.Count = 1 Then Exit For
Worksheets(MyList(i, 1)).Delete
Next i


End Sub

KongUK
10-17-2017, 02:32 AM
Works perfectly!

Thank you very much

mancubus
10-17-2017, 04:37 AM
you are welcome.
thanks for the feedback.
pls mark the thread as solved for future references...