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.