Copying and pasting a dynamic range (VBA)

[wengie]

Hope you guys can help me out with this

Background:
I am trying to build an activity manager for my sales team. Each one of them will hold a standardize template in which then they will update their appointments and then saved into a general folder. I then want to consolidate my entire team's activities into one sheet so that I have the master view

Where I am:
I am able to write a script that opens all files, copies and pastes perfectly. However this is where the problem starts. I am unable to write a script that identifies the range to copy (row and column). Currently all I have is a static range

Case in point: (this is a sample of what a sales person would fill in on the daily

With each passing day, the list will grow longer downwards

And I want to consolidate all my files, so the consolidated view looks something like this

Can someone help me out with my script?

It is as such

-------------------------------------------------------------------

Sub ActivityManager()
Dim MyFile As String
Dim erow
Dim Filepath As String
Filepath = "C:\Pipeline Manager\"
MyFile = Dir(Filepath)

Do While Len(MyFile) > 0
If MyFile = "zConsolidation.xlsm" Then
Exit Sub
End If

Workbooks.Open (Filepath & MyFile)
Range("A2:G6").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, 7))

MyFile = Dir
Loop

-------------------------------------------------------------------

I believe the only remaining parts are in the ones highlighted in red. Help please?

Thank you

Best regards
Wengie