Possibly...
Code
Sub test()
Dim vFiles As Variant, v As Variant
Dim wb As Workbook, ws As Worksheet, sTabName As String
Const sPATH As String = "C:\temp" 'change as needed
vFiles = fnGetFiles(sPATH, "txt")
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
With ThisWorkbook
For Each v In vFiles
Set wb = Workbooks.Open(v, ReadOnly:=True)
sTabName = wb.Worksheets(1).Name
ThisWorkbook.Activate
If Evaluate("ISREF('" & sTabName & "'!A1)") Then .Worksheets(sTabName).Delete
wb.Worksheets(1).Copy after:=.Sheets(.Sheets.Count)
wb.Close
Next v
End With
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub
Private Function fnGetFiles(sPATH As String, Optional EXT As String) As Variant
Dim sFileName As String
With CreateObject("Scripting.Dictionary")
If EXT = vbNullString Then
sFileName = Dir(sPATH, vbNormal)
Else
sFileName = Dir(sPATH & "\*." & EXT & "*", vbNormal)
End If
Do While Not sFileName = vbNullString
.Item(sPATH & "\" & sFileName) = Empty
sFileName = Dir
Loop
fnGetFiles = .keys
End With
End Function
Display More