Loop thru files in directory and delete worksheet

[jeffreybrown]

I cannot resove what is wrong with this code. I get a subscript out of range run time error 9.

I would like to set the worksheet (in red), but this is when I get the r/t error 9.

I removed the part in red and replaced later on in the code with the sheet name in red combined with the delete.

I have three sheets in the directory and it works on the first workbook, well at least it deletes the sheet, but I get a privacy warning, "The document contains macros, ActiveX controls, etc."

In the directory I want to exclude workbook 1040.xlsm. Any thoughts?

Sub Test()
    Dim strPath As String
    Dim strFile As String
    Dim wkb As Workbook
    Dim wks As Worksheet
    On Error Resume Next
    [COLOR="red"]Set wks = Sheets("1040")[/COLOR]    
    On Error GoTo 0

    strPath = ThisWorkbook.Path & Application.PathSeparator
    If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
    strFile = Dir(strPath & "*.xls*")

    Do While Len(strFile) > 0
        If strFile <> "1040.xlsx" Then
            Set wkb = Workbooks.Open(strPath & strFile)
            Application.DisplayAlerts = False
            [COLOR="red"]Sheets("1040")[/COLOR].Delete
            Application.DisplayAlerts = True
            wkb.Close savechanges:=True
            strFile = Dir
        End If
    Loop

    MsgBox "Completed...", vbInformation
    Set wks = Nothing
End Sub

 

[AlphaFrog]

You are trying to Set wks = Sheets("1040") before you open the workbooks in the directory. Shouldn't you Set wks after you open each workbook?

Or just try (On error resume next) to delete sheet 1040 after the workbook is open.

Sub Test()
    Dim strPath As String
    Dim strFile As String
    Dim wkb As Workbook
    Dim wks As Worksheet

    strPath = ThisWorkbook.Path & Application.PathSeparator
    If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
    strFile = Dir(strPath & "*.xls*")

    Do While Len(strFile) > 0
        If strFile <> "1040.xlsx" Then
            Set wkb = Workbooks.Open(strPath & strFile)
            Application.DisplayAlerts = False
[COLOR="Red"]            On Error Resume Next
                wkb.Sheets("1040").Delete
            On Error GoTo 0[/COLOR]
            Application.DisplayAlerts = True
            wkb.Close savechanges:=True
            strFile = Dir
        End If
    Loop

    MsgBox "Completed...", vbInformation
    Set wks = Nothing
End Sub

 

[jeffreybrown]

Ok tried this and it again delete's Sheets("1040") out of the first workbook, but after the first workbook has been opened, sheet deleted, and closed, it now finds workbook 1040.xlsx but does not skip it.

In the folder there are 4 files: 1.xlsx, 2.xlsx, 3.xlsx, 1040.xlsx

I would like to delete Sheets("1040") out of each workbook except for 1040.xlsx which will be the workbook where I run the code from.

I've stepped through the code and can see the first workbook selected is 1.xlsx and after it is finished the code selects 1040.xlsx but just hangs on that workbook and never progresses.

 

[AlphaFrog]

Move strFile = Dir outside of the IF statement.

Sub Test()
    Dim strPath As String
    Dim strFile As String
    Dim wkb As Workbook

    strPath = ThisWorkbook.Path & Application.PathSeparator
    If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
    strFile = Dir(strPath & "*.xls*")

    Do While Len(strFile) > 0
        If strFile <> "1040.xlsx" Then
            Set wkb = Workbooks.Open(strPath & strFile)
            Application.DisplayAlerts = False
            On Error Resume Next
                wkb.Sheets("1040").Delete
            On Error GoTo 0
            Application.DisplayAlerts = True
            wkb.Close savechanges:=True
        End If
        [COLOR="Red"]strFile = Dir[/COLOR]
    Loop

    MsgBox "Completed...", vbInformation
    Set wks = Nothing
End Sub

 

[jeffreybrown]

Thank you AlphaFrog that worked like a dream