Hi, I am struggling...I can't seem to be able to delete this one sheet when looping through all the workbooks in the subdirectory...please, anyone help...
Sub LoopAllExcelFilesInFolder()
'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them
'SOURCE:
www.TheSpreadsheetGuru.com
Dim wb As Workbook, ws As Worksheet
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Dim WorksheetName As String
'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
'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 = "*.xls"
'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
ActiveWorkbook.CheckCompatibility = False
Set wb = Workbooks.Open(Filename:=myPath & myFile)
Application.DisplayAlerts = False
'Find the excel worksheet 0100_Member_Tracker and delete it
If wb.Worksheets(WorksheetName).Name = "0100_Member_Tracker" Then
wb.Worksheets("0100_Member_Tracker").Delete
Else
End
End If
Application.DisplayAlerts = True
'Save and Close Workbook
MsgBox "Deleted 0100_Member_Tracker"
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.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub