Excel VBA: Delete Same Sheet from Multiple Workbooks

In Microsoft Excel, we may face a situation where we have to delete sheets from multiple workbooks. We can do this manually. But this process becomes hectic if we do this. Here comes VBA to play. We can delete sheets from multiple workbooks with a simple VBA code. It can reduce your stress and also your valuable time. In this tutorial, you will learn to delete the same sheet from multiple workbooks with Excel VBA.

This tutorial will be on point with suitable examples and proper illustrations. Make sure to read the whole article and practice this. It will develop your Excel knowledge.

Delete Same Sheet from Multiple Workbooks with Excel VBA: Step-by-Step Procedure

In the following sections, we are going to guide you in building the VBA code that will delete the same sheet from multiple workbooks in Excel. We recommend you learn and apply all these to your workbooks. It will definitely enrich your knowledge.

Before we start, let us introduce you to our workbooks. Now, we have three workbooks for this problem. And all of them have the same sheet named “Sales“.

This is Workbook 1:

Here, is Workbook 2:

And finally, this is Workbook 3:

As you can see, all the workbooks contain the same sheet named  “Sales”. Now, our goal is to delete the same sheet from these multiple workbooks using VBA code in Excel. Let’s get into it.

1. Build the Code

Step 1: Open the VBA Editor

📌 Steps

• First, press Alt+F11 on your keyboard to open the VBA editor.
• Then, select Insert>Module.

Read More: Excel VBA: Delete Sheet If It Exists

Step 2: Create Sub Procedure

First, create the subprocedure.

Sub delete_same_sheet()

End Sub

In this subprocedure, we will write all the codes.

Step 3: Declare Necessary Variables

Sub delete_same_sheet()

Dim wrkbook As Workbook
Dim wrksheet As Worksheet
Dim sheet_name As String

End Sub

Step 4: Take User Input

Sub delete_same_sheet()

Dim wrkbook As Workbook
Dim wrksheet As Worksheet
Dim sheet_name As String

sheet_name = Application.InputBox("Type the Sheet Name That You Want to Delete:")

End Sub

Step 5: Set DispayAlerts to False

Sub delete_same_sheet()

Dim wrkbook As Workbook
Dim wrksheet As Worksheet
Dim sheet_name As String

sheet_name = Application.InputBox("Type the Sheet Name That You Want to Delete:")

Application.DisplayAlerts = False

End Sub

Step 6: Loop Through Each Workbook

Sub delete_same_sheet()

Dim wrkbook As Workbook
Dim wrksheet As Worksheet
Dim sheet_name As String

sheet_name = Application.InputBox("Type the Sheet Name That You Want to Delete:")
Application.DisplayAlerts = False

For Each wrkbook In Workbooks

Next

End Sub

Step 7: Loop Through Each Worksheet

Sub delete_same_sheet()

Dim wrkbook As Workbook
Dim wrksheet As Worksheet
Dim sheet_name As String

sheet_name = Application.InputBox("Type the Sheet Name That You Want to Delete:")
Application.DisplayAlerts = False

For Each wrkbook In Workbooks
    For Each wrksheet In wrkbook.Worksheets

    Next
Next

End Sub

Step 8: Check Whether Sheet Name Is Same or Not

Sub delete_same_sheet()

Dim wrkbook As Workbook
Dim wrksheet As Worksheet
Dim sheet_name As String

sheet_name = Application.InputBox("Type the Sheet Name That You Want to Delete:")
Application.DisplayAlerts = False

For Each wrkbook In Workbooks
    For Each wrksheet In wrkbook.Worksheets
        If wrksheet.Name = sheet_name Then

        End If
    Next
Next

End Sub

Step 9: Delete the Sheet

Sub delete_same_sheet()

Dim wrkbook As Workbook
Dim wrksheet As Worksheet
Dim sheet_name As String

sheet_name = Application.InputBox("Type the Sheet Name That You Want to Delete:")
Application.DisplayAlerts = False

For Each wrkbook In Workbooks
    For Each wrksheet In wrkbook.Worksheets
        If wrksheet.Name = sheet_name Then

            wrksheet.Delete

        End If
    Next
Next

End Sub

Step 10: Set DisplayAlerts to True (Final Code)

Sub delete_same_sheet()

Dim wrkbook As Workbook
Dim wrksheet As Worksheet
Dim sheet_name As String

sheet_name = Application.InputBox("Type the Sheet Name That You Want to Delete:")
Application.DisplayAlerts = False

For Each wrkbook In Workbooks
    For Each wrksheet In wrkbook.Worksheets
        If wrksheet.Name = sheet_name Then
            wrksheet.Delete
        End If
    Next
Next

Application.DisplayAlerts = True

End Sub

Read More: How to Delete a Worksheet with No Prompt Using Excel VBA

2. Run the Code

Now, it’s time to run the code. First, press Alt+F8 on your keyboard. It will open a Macro dialog box.

Select delete_same_sheet and click on Run.

Now, type the sheet name you want to delete. Here, we want to delete the “Sales” sheet. Click on OK.

Now, check all the workbooks.

This is Workbook 1:

Here is Workbook 2:

And finally, this is Workbook 3:

As you can see, our VBA code worked perfectly to delete the same sheet from multiple workbooks in Excel.

Read More: Excel VBA: Delete Sheet If It Contains Name

💬 Things to Remember

✎ Basically, we deleted the sheet from multiple workbooks based on the sheet name. This code will delete the same sheet based on names. It will delete the even if the sheet content might be different. So, be cautious before running the code.

✎ To delete sheets from multiple workbooks, you must keep open all the workbooks.

Download Practice Workbook

Conclusion

To conclude, I hope this tutorial has provided you with a piece of helpful knowledge to delete the same sheet from multiple workbooks using VBA code in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Keep learning new methods and keep growing!