Hi,

I have multiple excel workbooks that I need to copy into a masterfile within the same folder.

Each workbook would have the same template as the masterfile.

There are 4 sheets in each workbook that need to be transferred specifically to the respective sheet in the masterfile.

I have tried modifying codes such as the one below mentioned, but i can't get it to work.

Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
Dim Filepath As String
Filepath = "C:\Test\"
MyFile = Dir(Filepath)
Do While Len(MyFile) > 0
If MyFile = "TEST.xlsm" Then
Exit Sub
End If

Workbooks.Open (Filepath & MyFile)
Range("A2:D2").Copy
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 4))


Loop

End Sub

Any help would much appreciated.

Thank you.