Option Explicit
Sub Macro1()
Dim strSrcPath As String, strFile As String
Dim wb As Workbook
Dim blnWasFileOpen As Boolean
Dim wsSrc As Worksheet, wsDestin As Worksheet
Dim varSheets As Variant
Dim i As Long
Dim strCopyRange As String
strSrcPath = "C:\Users\yrga88\Desktop"
strSrcPath = IIf(Right(strSrcPath, 1) <> "\", strSrcPath & "\", strSrcPath)
strFile = Dir(strSrcPath & "*.xls*")
varSheets = Array("abc", "def", "ghi")
Application.ScreenUpdating = False
If Dir(strSrcPath, vbDirectory) = "" Then
MsgBox "The path """ & strSrcPath & """ is invalid." & vbNewLine & "Please check and try again.", vbExclamation
Exit Sub
End If
Do While Len(strFile) > 0
On Error Resume Next
Set wb = Workbooks(CStr(strFile))
If Err.Number <> 0 Then
Set wb = Workbooks.Open(strSrcPath & strFile, UpdateLinks:=False, ReadOnly:=True)
Else
blnWasFileOpen = True
End If
On Error GoTo 0
For Each wsSrc In wb.Worksheets
If IsNumeric(Application.Match(wsSrc.Name, varSheets, 0)) Then
Call UnhideShowAll(wsSrc)
Set wsDestin = ThisWorkbook.Sheets(CStr(wsSrc.Name))
Call UnhideShowAll(wsDestin)
strCopyRange = Split(wsSrc.UsedRange.Offset(1, 0).Address, ":")(0)
strCopyRange = strCopyRange & ":" & Split(wsSrc.UsedRange.Address, ":")(1)
On Error Resume Next
i = wsDestin.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
i = IIf(i = 0, 2, i)
On Error GoTo 0
wsSrc.Range(strCopyRange).Copy Destination:=wsDestin.Range("A" & i)
End If
Next wsSrc
If blnWasFileOpen = False Then
Application.DisplayAlerts = False
wb.Close SaveChanges:=False
Application.DisplayAlerts = True
End If
blnWasFileOpen = False
strFile = Dir
Loop
Application.ScreenUpdating = True
MsgBox "Files have now been consolidated.", vbInformation
End Sub