VBA copy data from another workbook into current workbook (specific tab)

TTC-BlueHill1 

Open the "Troy Corporation Carrier Review March 2024.xlsx" workbook.

Create a new workbook

Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module.

Copy the code listed below into the module.

Then switch back to Excel.

If you want to be able to run the macro again later, save the new workbook as a macro-enabled workbook (*.xlsm). Otherwise, just run the macro, then discard the new workbook.

Sub ImportData()
    Dim wbkSource As Workbook
    Dim wshSource As Worksheet
    Dim lngLastSourceRow As Long
    Dim wbkTarget As Workbook
    Dim wshTarget As Worksheet
    Dim lngTargetRow As Long
    Dim strFolder As String
    Dim strFile As String

    Application.ScreenUpdating = False
    Set wbkTarget = Workbooks("Troy Corporation Carrier Review March 2024.xlsx")
    Set wshTarget = wbkTarget.Worksheets("Detail")
    On Error Resume Next
    lngTargetRow = 2
    ' Path of the subfolder with the source workbooks
    strFolder = "N:\MyFolder\MySubfolder\"
    strFile = Dir(strFolder & "*.xls*")
    Do While strFile <> ""
        Set wbkSource = Workbooks.Open(Filename:=strFolder & strFile, ReadOnly:=True)
        Set wshSource = wbkSource.Worksheets(1)
        lngLastSourceRow = wshSource.Range("E:AE").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        wshSource.Range("E3:AE" & lngLastSourceRow).Copy Destination:=wshTarget.Range("A" & lngTargetRow)
        Application.CutCopyMode = False
        wbkSource.Close SaveChanges:=False
        lngTargetRow = lngTargetRow + lngLastSourceRow - 2
        strFile = Dir
    Loop
    Application.ScreenUpdating = True
End Sub