Getting Run-time error '9': when trying to open workbook and delete all sheets but 1

[grippy10]

I'm trying to delete sheets 1,2,4,5 and make sheet 3 the active one. I get the error on " Sheets(1).Select" and "Sheets(Array(1, 2, 4, 5)).Select". I get a run time error when I try and run the following macro. Help?


zFolder = zPath & "\Kitty*"
Application.DisplayAlerts = False
Sheets(1).Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets(Array(1, 2, 4, 5)).Select
Sheets(1).Activate
ActiveWindow.SelectedSheets.Delete
On Error Resume Next
Sheets("regress").Delete
On Error GoTo 0
Application.DisplayAlerts = True

[MikeTheBike]

@grippy10
Hi

I have more questions than answers, but this will do what you have described.

Sub DeleteActiveBookSheetsExceptSheet3()
    Dim i As Integer
    Application.DisplayAlerts = False
        With ActiveWorkbook
            For i = .Sheets.Count To 1 Step -1
                If i <> 3 And .Sheets.Count > 1 Then .Sheets(i).Delete
            Next i
        End With
    Application.DisplayAlerts = True
End Sub

With some mods you can probably bend it to suit your requirements!!


MTB