Copy paste from one worbook to another workbook

Someone must have moved your post to a new thread.

This simple example uses the FileSearch object to return a list of Excel files, then copies data from columns A & B in each one to the master sheet.

I suggest you try this code out on a test workbook with a few test files all in their own folder. If you single step the code with the F8 key in the VB editor, you can see what's going on.

Sub Read_Workbooks()
    Dim intFile As Integer
    Dim wsMaster As Worksheet
    Dim strThisWBName As String
    Dim strFileToOpen As String
    
    Application.ScreenUpdating = False
    
    strThisWBName = ThisWorkbook.Name
    Set wsMaster = ThisWorkbook.Worksheets(1)       'data will be copied to sheet1 of Master WB
    
    With Application.FileSearch
        .NewSearch                          'clear previous search
        .FileType = msoFileTypeExcelWorkbooks
        .LookIn = ThisWorkbook.Path        'look in same path as this workbook
        .SearchSubFolders = False
        If .Execute > 0 Then
            For intFile = 1 To .FoundFiles.Count
                strFileToOpen = .FoundFiles(intFile)
                If InStr(strFileToOpen, strThisWBName) = 0 Then 'check if trying to open this workbook
                    Transfer_Data strFileToOpen, wsMaster  'transfer from source worksheet to Master ws
                End If
            Next
        Else
            MsgBox "No files found."
        End If
    End With
    
    Application.ScreenUpdating = True
    Set wsMaster = Nothing
End Sub

Sub Transfer_Data(sceFile As String, dstWS As Worksheet)
    'transfers first 2 columns of data from sceFile to the master sheet
    Dim intRow As Long
    Dim LastRowInDest As Long, LastRowInSce As Long
    Dim sceWB As Workbook
    Dim sceWS As Worksheet
    
    Application.EnableEvents = False            'suppress workbook_open event
    
    Set sceWB = Workbooks.Open(sceFile)
    Set sceWS = sceWB.Worksheets(1)
    
    LastRowInDest = dstWS.Cells(Rows.Count, "A").End(xlUp).Row 'get last used row in column A of master sheet
    LastRowInSce = sceWS.Cells(Rows.Count, "A").End(xlUp).Row   'get last used row in column A of source sheet
    
    'transfer data to summary sheet
    For intRow = 1 To LastRowInSce
        dstWS.Cells(LastRowInDest + intRow, 1).Value = sceWS.Cells(intRow, 1).Value   'transfer row intRow, column A
        dstWS.Cells(LastRowInDest + intRow, 2).Value = sceWS.Cells(intRow, 2).Value   'transfer row intRow, column B
    Next
    
    sceWB.Saved = True
    sceWB.Close
    
    Application.EnableEvents = True
    Set sceWS = Nothing
    Set sceWB = Nothing
End Sub