A.N.M. Mohaimen Shanto
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:
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 (4 Methods)
Step 2: Create Sub Procedure
First, create the subprocedure.
Sub delete_same_sheet()
End SubIn 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 SubHere, 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.
Read More: How to Delete a Sheet in Excel (5 Ways)
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 SubWe 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 SubApplication.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.
Read More: Shortcut to Delete Sheet in Excel (5 Quick Examples)
Similar Readings
- Excel VBA: Delete Sheet If It Contains Name (4 Ways)
- How to Delete Hidden Sheets in Excel (2 Effective Methods)
- Undo Delete Sheet in Excel (3 Methods)
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 SubBy 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 SubBy 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 SubIf 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.
Read More: Excel VBA: Delete Sheet If It Exists (4 Methods)
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 Subwrksheet.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 SubYou always have to set the DisplayAlert to True at the end of the code.
Read More: How to Delete Excel Sheet Using VBA (10 Suitable Ways)
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: [Fixed!] Delete Sheet Not Working in Excel (2 Solutions)
💬 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!
