Excel VBA: Delete Same Sheet from Multiple Workbooks

Get FREE Advanced Excel Exercises with Solutions!

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: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.

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!


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo