Forum Discussion

CK1001495's avatar
CK1001495
Copper Contributor
Dec 10, 2023

Deleting worksheets from multiple workbooks

Hi, I have approx 100 workbooks all with the same formatting. 3 work sheets in each with the same uniform names. (Alpha, Beta and Omega)

 

I need to delete all the worksheets called Alpha and Beta (or else extract all the worksheets called Omega). Is there a way to do that? 

excel
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Dec 10, 2023

    CK1001495 

    If all workbooks are in the same folder, with no other workbooks (or if there are other workbooks in the folder, they do not contain sheets named Alpha and Beta), you can use a macro in the desktop version of Excel for Windows or Mac.

    Here is a version for Windows:

    Sub DeleteAlphaBeta()
    Dim strFolder As String
    Dim strFile As String
    Dim wbk As Workbook

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    ' Edit the path of the folder. Keep the trailing backslash
    strFolder = "C:\Excel\Test\"
    strFile = Dir(strFolder & "*.xls*")
    Do While strFile <> ""
        Set wbk = Workbooks.Open(Filename:=strFolder & strFile)
        On Error Resume Next
        wbk.Worksheets(Array("Alpha", "Beta")).Delete
        On Error GoTo ErrHandler
        wbk.Close SaveChanges:=True
        strFile = Dir
    Loop

ExitHandler:
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Exit Sub

ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

Resources