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.


Download Practice Workbook

Download these workbooks while practicing the problem.


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

In the following sections, we are going to guide you to build 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:delete same sheet from multiple workbooks in excel vba

Here, is Workbook 2:

And finally, this is Workbook 3:

delete same sheet from multiple workbooks in excel vba

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.


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

Here, we declared all the variables.

wrkbook: It will store the workbooks.

wrksheet: It will store the worksheets.

sheet_name: It will store the sheet name that we want to delete from these multiple workbooks.


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

We will take the sheet name as input from the user using this input box.


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

Application.DisplayAlerts = False: When you try to delete the sheet from a workbook, Excel will show you the following warning message:

delete same sheet from multiple workbooks in excel vba

Now, as we are deleting multiple workbooks, it will appear multiple times. So, we are setting the DisplayAlerts property to False so that it doesn’t appear.


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

By this loop, we will go through each workbook to delete the sheet.


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

By this loop, we will go through each worksheet of each workbook. If you enter the first workbook, this loop will travel through each sheet from the first workbook. And the same for the other workbooks.


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

If wrksheet.Name = sheet_name Then: It will check for the sheet by its name provided by the user. As we want to delete the sheet “Sales”. Our code will go through each workbook and search by the sheet name.


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

wrksheet.Delete: If it finds the sheet, it will delete that sheet from that workbook. Otherwise, the loop goes on.


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

You always have to set the DisplayAlert to True at the end of the code.


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.

delete same sheet from multiple workbooks in excel vba

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:

delete same sheet from multiple workbooks in excel vba

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


💬 Things to Remember

✎ Basically, what we did is, 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.


Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful 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.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo