Excel tutorials

6. Delete the last worksheet in multiple workbooks within a single folder

Continue tutorials from the previous web page. In this part of the tutorial, the fourth in the order I will show how we can find, remove the last worksheet in each workbook that is located in the same folder.

In our case (if you follow the tutorial from the beginning) I have added temporary extra worksheet 'Sheet5' which I used to create multiple workbooks from the list that was in column 'A'. This work sheet is no longer needed. We will now delete specific Sheet from multiple Workbooks it using the VBA macro below.

Option Explicit
'published by www.ic-ims.com
Sub LoopAllExcelFilesInFolder()
'https://stackoverflow.com
'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them
Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
'Retrieve Target Folder Path From User
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & "\"
End With
'In Case of Cancel
NextCode:
myPath = myPath
If myPath = "" Then GoTo ResetSettings
'Target File Extension (must include wildcard "*")
myExtension = "*.xlsm"
'Target Path with Ending Extention
myFile = Dir(myPath & myExtension)
'Loop through each Excel file in folder
Do While myFile <> ""
'Set variable equal to opened workbook
Set wb = Workbooks.Open(Filename:=myPath & myFile)
'--------------- Your action -------------
Call DeleteLastSheet
'-----------------------------------------
'Save and Close Workbook
wb.Close SaveChanges:=True
'Get next file name
myFile = Dir
Loop
'Message Box when tasks are completed
MsgBox "Task Complete!"
ResetSettings:
'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Sub DeleteLastSheet()
On Error GoTo err
Application.DisplayAlerts = False 'Disable excel alerts sent while deleting a sheet
Worksheets(Worksheets.Count).Delete
'MsgBox ("Last Sheet is successfully deleted")
err:
Application.DisplayAlerts = True 'Enable excel alerts
End Sub

After starting VBA macros, select a folder that contains multiple workbooks. Wait a certain time. I applied this VBA macro to 100 Excel files and the duration of Excel's work was about 2 minutes.

The next step will be to delete a specific 'Module1' in multiple workbooks in the same folder. If you've been following the whole tutorial from the beginning of this 'Module1' I set up a VBA macro that duplicated 'template.xlsm' 100 times.

List all topics related to complete tutorials

  1. Creating Top Folders and Sub Folders in One Step
  2. How to copy a workbook 100 times and create multiple workbooks from the list in the 'A' column
  3. Rename the first in the order of the worksheet, in multiple workbooks in the same folder
  4. How to remove the last Worksheet in multiple Workbooks in the same folder
  5. How to remove a particular VBA module in multiple workbooks in the same folder
  6. How to save all workbooks as values without formulas in all sub-folders

Click to continue tutorials: ------ >>>>>> The next part of the tutorial Item 5 from list above