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