Copying and pasting a dynamic range

[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)
13537
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
13536

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

[Yongle]

Welcome to the forum.
Dynamic ranges
There are lots of ways to create dynamic ranges. I tend to favour using the Excel Table functionality, because it allows you to add more lines to the data and it uses formula in previous lines and copies down automatically as you add more lines. Also if you can use your table as the range and as you add more lines the range expands dynamically.
(to create a table create your headers, add a line of data, put cursor anywhere in that range and (tab) Insert / Table (click "my table has headings")
Attached workbook
Sheet" consol1" is after the first consolidation, added more data to get "consol2" and then "consol3". I have coloured the data so that you can see the various stages
Salesmen sheets for Albert, Peter and John include dummy data contained in a Table. I named their respective ranges (=full table except header) Albert, Peter and John
The code
Array called "All" is populated with their range names so that we can loop through and copy each range in turn to the consolidation worksheet.
Variable nR captures the next row to paste into.

See if macro does what you want by:
1 Adding a few lines of data to each salesman's sheet
2 Select sheet Consol4 (Macro runs from active sheet)
3 Run the macro

Adapting
I have kept this pretty simple, with everything in one file. Your data will be in multiple files.
If the macro does what you want, come back if you need help adapting it.



Sub Consol_Sales_Activity()


Dim nR As Long
Dim All() As Variant
ReDim All(2)
All(0) = "Albert"
All(1) = "John"
All(2) = "Peter"

With ActiveSheet
Sheets("Headers").Range("A1:I1").Copy
.Range("A1").PasteSpecial xlAll


For i = 0 To UBound(All)
Range(All(i)).Copy
nR = .Range("A1000000").End(xlUp).Row + 1
.Range("A" & nR).PasteSpecial xlAll
Next i
End With


End Sub

[snb]

@Yongle:


sn=split("Albert John Peter")

[Yongle]

@snb - Noted