VBA - Loop to consolidate lists from multiple workbooks into a consolidated table

[anthonygws]

Hello,

I am trying to code a macro that will consolidate data from multiple workbooks into another one, detecting the last row to select only the relevant range, then detecting the last row in the consolidation table to paste after the last row.

I get stuck at the copy paste lines and will be eternally grateful if one could help me.

Here is the code

Sub Consolidate() Dim Path As String Dim Filename As String Dim Sheet As Worksheet Dim Sht As Worksheet, DstSht As Worksheet Dim LstRow As Long, LstCol As Long, DstRow As Long Path = "C:\Users\UserName\Documents\SourceFolder\" Filename = Dir(Path & "*.xlsx") Do While Filename <> "" Workbooks.Open Filename:=Path & Filename, ReadOnly:=True LstRow = ActiveWorkbook.Sheets(1).Cells.Find(What:="*", _ After:=Range("A1"), _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row DstRow = ThisWorkbook.Sheets(1).Cells.Find(What:="*", _ After:=Range("A1"), _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row DstRow = DstRow + 1 ActiveWorkbook.Sheets("Time").Range(Cell(A2) : Z & LstRow).Copy ThisWorkbook.Sheets("Consolidation").Range("A & DstRow : Z & DstRow + LstRow").Paste Workbooks(Filename).Close Filename = Dir() Loop End Sub _____________ Thank you very much ! Anthony