VBA Code to delete specific worksheets in multiple workbooks

VBA Code to delete specific worksheets in multiple workbooks

December 25, 2017

You can use the visual basic code posted below (see this original post on stackoverflow) to delete a specified worksheet (the second, the third, the fourth, etc.) in a group of workbooks that you have saved in a particular folder. 

 

So if we start with the a group of Excel files like this:

 

 

. . . and enter this code in a new module in Visual Basic:

 

 . . . . and modify the line beginning wb.Worksheets to set a specific worksheet number, the macro will then run and delete each worksheet in the folder.    It will prompt you to select the folder you want it to run on, and also make you confirm that each specified worksheet should be deleted.

 

 

 

When it's complete, you'll get this message:

 

 If each workbook in the folder doesn't have the specified worksheet number, the macro will crash.

 

 

Sub LoopAllExcelFilesInFolder()

'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
  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
      Set wb = Workbooks.Open(Filename:=myPath & myFile)

    'Change First Worksheet's Background Fill Blue
      'wb.Worksheets(1).Range("A1:Z1").Interior.Color = RGB(51, 98, 174)
      wb.Worksheets(2).Delete

    '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.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

Please reload

Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

© 2015 by Sean O'Shea . Proudly created with Wix.com