Copy & Paste all from all workbooks in a folder to new worksheet

[sjk1193]

Hi there,

I need to specify a date and if it matches a date in column A in a different workbook I need to copy the whole row and paste it in a different workbook, and then loop through and copy/paste. Ideally I need to cycle through all workbooks in the same folder and copy paste from each of them.

Cross post: https://www.excelforum.com/excel-pr...1262086-copy-and-paste-based-on-date-vba.html

So far I have the code below but for some reason its not copying or pasting

Sub CopyRows()


Dim InputFile As Workbook
Dim OutputFile As Workbook
Dim myDate As Variant
Dim lr2 As Long
Dim i As Long




Set OutputFile = ThisWorkbook
Set InputFile = Workbooks.Open("S:\xxxxx\xxxx.xlsx")






myDate = InputBox("Enter Date    (mm/dd/yyyy)")




InputFile.Sheets(1).Activate


For i = 3 To Cells(Rows.Count, 1).End(xlUp).Row
    If myDate = Sheets(1).Cells(i, 1) Then
        'If Sheet1.Cells(i, 1).Value = myDate Then
        Rows(i).EntireRow.Copy
        
        OutputFile.Sheets(1).Activate
            lr2 = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
        
        InputFile.Sheets(1).Activate
        
    End If
         
Next i


End Sub