Thread: Delete A Worksheet From All Workbooks In A Folder

I have been looking for a piece of code that will delete a worksheet from every workbook in a folder, but cannot find any examples.

The worksheet may not exist, so if it does not the code should move onto the next workbook in the folder, no error messages are required.

Can anybody help me.

Hi AlexRoberts, try

Sub AlexRoberts()
    Dim ShtCnt As Long, ShtCntr As Long
    ShtCnt = ThisWorkbook.Sheets.Count
    For ShtCntr = 1 To ShtCnt
        If Sheets(ShtCntr).Name = "DeleteMe" Then
            Sheets(ShtCntr).Delete
        End If
    Next ShtCntr
End Sub

Here is code that will do that. It is well commented so you know what each line of code does. You need to change the folder path to your folder, and the sheet name to your sheet you are looking to delete.

Public Sub DeleteWorksheets()

    'Declare local variables.
    Dim strCurPath As String
    Dim strFile As String
    Dim ws As Worksheet
    
    'Turn off screen updating so the code will run faster.
    Application.ScreenUpdating = False
    

    'Set up a dummy variable to hold the current path.
    strCurPath = "C:/YourFolder/"
    'Get the first file in the path.
    strFile = Dir(strCurPath & "*.xls")
        
    'Loop through each file in the directory.
    Do While strFile <> ""
        'Turn events off so we aren't asked to update links or no workbook.open macros are run.
        Application.EnableEvents = False
        'Open the workbook that is being examined.
        Workbooks.Open strCurPath & strFile, False
        'Turn events back on.
        Application.EnableEvents = True
        'Loop through each worksheet in the current workbook.
        For Each ws In ActiveWorkbook.Worksheets
            'Check if the worksheet is the one you want to delete.
            If ws.Name = "SheetName" Then
                'Delete the sheet.
                ws.Delete
            End If
        Next ws
        'Close the workbook.
        ActiveWorkbook.Close True
        'Go to the next file.
        strFile = Dir()
    Loop
    
    'Turn screen updating back on.
    Application.ScreenUpdating = True

End Sub

Public Sub DeleteWorksheets()

    'Declare local variables.
    Dim strCurPath As String
    Dim strFile As String
    Dim ws As Worksheet
    
    'Turn off screen updating so the code will run faster.
    Application.ScreenUpdating = False
    

    'Set up a dummy variable to hold the current path.
    strCurPath = "C:/YourFolder/"
    'Get the first file in the path.
    strFile = Dir(strCurPath & "*.xls")
        
    'Loop through each file in the directory.
    Do While strFile <> ""
        'Turn events off so we aren't asked to update links or no workbook.open macros are run.
        Application.EnableEvents = False
        'Open the workbook that is being examined.
        Workbooks.Open strCurPath & strFile, False
        'Turn events back on.
        Application.EnableEvents = True
        'Check if the workbook opened as read only.
        If ActiveWorkbook.ReadOnly = True Then
            'If so, list it in the first available row in column A of sheet 1.
            ThisWorkbook.Sheets("Sheet1").Cells(ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).Value = ActiveWorkbook.Name
            'Close the workbook without saving.
            ActiveWorkbook.Close False
        'If not opened in read only, search for worksheet to delete.
        Else
            'Loop through each worksheet in the current workbook.
            For Each ws In ActiveWorkbook.Worksheets
                'Check if the worksheet is the one you want to delete.
                If ws.Name = "SheetName" Then
                    'Delete the sheet.
                    ws.Delete
                End If
            Next ws
            'Save and close the workbook.
            ActiveWorkbook.Close True
        End If
        'Go to the next file.
        strFile = Dir()
    Loop
    
    'Turn screen updating back on.
    Application.ScreenUpdating = True

End Sub

If you have an issue Amarjeet, please read the forum rules (specifically # 2) and then start a new thread. You can post a link to this thread if you feel it contains relevant information.