VBA to delete specific sheet in folder of 300+ files

[dwalls]

Hello,

I have a folder where I need to delete 300+ "2022" sheets. I have built a macro to add in new year sheets (2024,2025, etc.), however, I am hoping you all can help me with building a macro to delete certain sheet names. Some folders won't contain 2022 sheets so I am trying to find something that will account for the error it will throw if the workbook it opens doesn't contain that specific sheet. Thank you all!

 

[Micron]

I have built a macro to add in new year sheets (2024,2025, etc.),

I presume that means you already have code to loop over each wb in the folder, open the wb and add a sheet. Start with that to open each wb then loop over the sheets in the opened wb. Test each sheet name in a loop inside of that loop. If it equals what you're looking for, delete it, save wb and close. This won't raise an error because you're not trying to delete something that doesn't exist. You probably already have dealt with saving the wb in your existing code so replicate that as well.

 

[dwalls]

I am newer to VBA; I have mostly just been piecing things together. Attached is my current macro. Where would I tell it to delete the "2022" sheet?

 

[Micron]

I like to put all my declarations at the top, so there, add
Dim sht As Worksheet
after the copy line:

For Each sht in destinationWorkbook
   If sht.Name = "2022" Then sht.Delete
Next

then continue on with the close file lines and the rest. This assumes the 2022 sheet is in the wb that you are opening with that code. I would copy the parent folder for this so as to have a backup in case this goes bad. I take no responsibility for anything that goes wrong with important data when someone asks for code that deletes things.

Please use code tags (vba button on posting toolbar) to maintain indentation in posted code. Pics of code are often of little use. Some people will not type out a lot of code in a picture in order to solve an issue - posting code as I did is much better.

EDIT - what's interesting is that you didn't seem to need to deal with any prompts about sheet insert. You might have to when deleting.

 

[dwalls]

Public Sub CopySheetToAllWorkbooksInFolder()

    Dim sourceSheet As Worksheet
    Dim folder As String, filename As String
    Dim destinationWorkbook As Workbook
    Dim sht as Worksheet
   
    'Folder containing the 300+ workbooks
   
    folder = "File Path"
      
    filename = Dir(folder & "*.xlsm", vbNormal)
    While Len(filename) <> 0
        Debug.Print folder & filename
        Set destinationWorkbook = Workbooks.Open(folder & filename)
        For Each sht in destinationWorkbook
         If sht.Name = "2022" Then sht.Delete
    Next
        destinationWorkbook.Close True
        filename = Dir()  ' Get next matching file
    Wend

End Sub

I wouldn't blame you for anything that got deleted. I appreciate the help. I am new to this forum, so I was unaware of the code tags (still don't think I used it right).

The above VBA is what I have currently. I deleted some of the lines from the original code since i'm not wanting to copy a sheet into the workbooks. How do you think I could address the prompts or if some workbooks don't contain a "2022" sheet.

I appreciate the help. I could go into each sheet and delete manually but would really like to learn how to do this if the opportunity arises again.

 

[Micron]

How do you think I could address the prompts

What prompts? Possibly by suppressing alerts. Depends on what prompts you get.

or if some workbooks don't contain a "2022" sheet

I think I covered this already?

Test each sheet name in a loop inside of that loop. If it equals what you're looking for, delete it, save wb and close. This won't raise an error because you're not trying to delete something that doesn't exist.

The thinking is that as you loop over the wb's in folder, you loop over the sheets in the opened wb and only attempt to delete IF it finds one by the name you specify.