Option Explicit
Sub ImportFirstSheetFromFilesInDirectory()
Dim Path As String 'string variable to hold path to look through
Dim FileName As String 'temporary filename variable
Dim WBk1 As Workbook 'this workbook
Dim WBk2 As Workbook 'temporary workbook (each workbook in the directory)
'***** MANUALLY SET FOLDER TO CYCLE THROUGH *************
Path = "V:\test_MonthEnd" '<-- put your directory here
'********************************************************
Application.EnableEvents = False 'turn off events
Application.ScreenUpdating = False 'turn off screen updating
If Right(Path, 1) <> Application.PathSeparator Then 'if path doesn't end in "\"
Path = Path & Application.PathSeparator 'add "\"
End If
Set WBk1 = ActiveWorkbook 'this workbook
FileName = Dir(Path & "*.xls", vbNormal) 'set first file's name to filename variable
Do Until FileName = "" 'loop until all files have been done
If Path <> ThisWorkbook.Path Or FileName <> ThisWorkbook.Name Then
Set WBk2 = Workbooks.Open(FileName:=Path & FileName) 'open file, set to WBk2 variable
WBk2.Sheets(1).Copy After:=WBk1.Sheets(WBk1.Sheets.Count) 'the newly opened workbook will be active
WBk2.Close 'the original workbook will be active
End If
FileName = Dir() 'set next file name to filename variable
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
Set WBk2 = Nothing
End Sub