Copying and pasting dynamic ranges
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 where it copies the range, and also identifying where to paste. Help please?
Thank you
Best regards
Wengie
Recent comments
5 years 36 weeks ago
6 years 22 weeks ago
6 years 34 weeks ago
6 years 37 weeks ago
6 years 38 weeks ago
6 years 43 weeks ago
6 years 52 weeks ago
7 years 2 days ago
7 years 3 days ago
7 years 3 days ago