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.
Bookmarks