Perhaps a macro like this? You need to change the line "E:\Temp\" to suit your needs.
Option Explicit
Sub ProcessAllFiles()
Dim sPath As String
Dim Wb As Workbook
Dim sFile As String
Dim i As Integer
sPath = "E:\Temp\"
sFile = Dir(sPath & "*.xls") 'changing "*.xls" to "*.xl??" for all kind of excel files
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Do While sFile <> ""
Set Wb = Workbooks.Open(sPath & sFile)
On Error Resume Next
Sheets("sales").Activate
If Err.Number = 9 Then
Wb.Close SaveChanges:=False
GoTo skipper
End If
Sheets("sales").Activate
ActiveSheet.UsedRange.Copy
Wb.Close SaveChanges:=False
Sheets(Mid(sFile, 1, Len(sFile) - 4)).Activate
i = ActiveSheet.UsedRange.Rows.Count
If i = 1 Then
Range("A1").PasteSpecial Paste:=xlPasteAll
Else
Range("A" & i + 1).PasteSpecial Paste:=xlPasteAll
End If
Application.CutCopyMode = False
skipper:
sFile = Dir
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Alf
Bookmarks