Hi Marc,

Thanks for the reminder.

The problem was due to the code not being run in a module.

Below is the current code now:

Sub LoopThroughDirectory()
Dim MyFile As String
Dim p As Integer, q As Integer, r As Integer, s As Integer

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)
    ActiveWorkbook.Sheets("sheet2").Activate
    Range("A2:D2").Copy
    ActiveWorkbook.Close
    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.paste Destination:=Worksheets("Sheet2").Range(Cells(erow, 1), Cells(erow, 4))

    
    MyFile = Dir
Loop

End Sub
Is it possible to copy a range from a specific worksheets in a workbook as paste it in the respective sheets in the masterfile?

As mentioned there a 4 different sheets to the copied and pasted respectively.

E.g.

Workbook1's sheet1's range = Master Workbook's sheet1's next empty row
Workbook1's sheet2's range = Master Workbook's sheet2's next empty row
Workbook1's sheet3's range = Master Workbook's sheet3's next empty row
Workbook1's sheet4's range = Master Workbook's sheet4's next empty row

then it continues to loop through the rest of the files in the folder

thank you