How to delete a worksheet from multiple files

[valesilver]

Hello, hope you can help with this.

I have 50 Excel workbooks, all containing two sheets. The first worksheet is called "Contents" across all workbooks, the second one has a unique name in each workbook (which matches the file name). I need to import the second sheet from all these files in one excel workbook.

I used to use Asap utilities to import multiple files in one workbook, however as far as I understand, this only imports the active sheet from each file. So whenever I try to import the above workbooks it only imports the "Contents" sheet from each of them, as this is the active one.

How can I delete the 'Contents' sheet from each file, so that I can then import the right sheets into one file?

As there are so many files, it does not make sense to open each file, click on enable editing, delete the '"Contents" sheet, save and close.

Ultimately what I need to do is to merge all the data from each second sheet into one big worksheet. I already have a macro for this, which takes all the sheets from my workbook and merge them together, keeping the first row of the first sheet as headers and getting rid of the first row of all the other sheets). But I need to get all the right sheets in one file first.

Could you please advice on how to do this? Thank you very much.

[LalitPandey87]

You can not delete any sheet without opening excel file. In your case what you can do is open each file pragmatically using VBA delete the sheet then save and close the workbook.

[mrmmickle1]

Try something like this:

Sub EditFileDirectory()

    Dim path        As String
    Dim ThisWB      As String
    Dim Filename    As String
    Dim Wkb         As Workbook
    Dim lastR       As Long
    
    'Toggle off for speed
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    On Error GoTo ErrorTrap
    ThisWB = ActiveWorkbook.Name
    path = "C:\Users\mmickle\Desktop\MyTest" 'Your File Directory Here
    Filename = Dir(path & "\*.xlsx", vbNormal)
    If Len(Filename) = 0 Then Exit Sub
    Do Until Filename = vbNullString
        If Not Filename = ThisWB Then
            Set Wkb = Workbooks.Open(Filename:=path & "\" & Filename)
                Sheets(2).UsedRange.Copy 'Copy Data....<---- You may need to use ActiveWorkBook.Name to get your sheet name
                Windows(ThisWB).Activate 'Activate Master WorkBook
                lastR = Sheets("MasterCompilation").Range("A" & Rows.Count).End(xlUp).Row 'Define last row <---Change Sheet Name Accordingly
                Range("A" & lastR + 1).PasteSpecial 'Paste on first open last row
                Windows(Filename).Activate 'Re-activate File to clear data
                Sheets(2).UsedRange.ClearContents 'clear data
            Wkb.Close True 'close file and save changes
        End If
        Filename = Dir() 'Go to next file in directory
    Loop
    
    'Toggle back on
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
    MsgBox "File editing is now complete", vbInformation, "ExcelFox- File Editor"
    On Error GoTo 0 'Reset Error Handling
    Exit Sub
    
ErrorTrap:
    
    MsgBox "Error Num: " & Err.Number _
    & Chr(13) & "Error Desc: " & Err.Description _
    & Chr(13) & Chr(13) & "An unexpected error occured.  Please contact" _
    & Chr(13) & "File Administrator to help diagnose the issue." _
        , vbCritical, "ExcelFox- Error Handler"
    
End Sub

[valesilver]

That looks interesting. I'll have a go with it. For now I've found a very basic macro for deleting a sheet, saving and closing a workbook, that I can apply to all my files, once they are all open. This is saving me quite a lot of time already.
Thank you for your helpful replies.