Hi everyone - I am really stuck and would really appreciate any support that can be offered. I have been stuck on this for ages and tried many different ways but cannot get it to work.
Basically I have multiple sheets of data with dates, names etc and i want to be able to press a button and pull all line entries within a specified date range into one table.
I am trying one sheet at a time of course but when i run the macro, it only pulls in one entry, i suspect the "For" statement is stopping as its found an entry that matches the condition but i want it to find and copy ALL of the entries that match the condition, hope this makes sense. Here is a snippet of the code i am using:
Basically I have multiple sheets of data with dates, names etc and i want to be able to press a button and pull all line entries within a specified date range into one table.
I am trying one sheet at a time of course but when i run the macro, it only pulls in one entry, i suspect the "For" statement is stopping as its found an entry that matches the condition but i want it to find and copy ALL of the entries that match the condition, hope this makes sense. Here is a snippet of the code i am using:
VBA Code:
Sub populate_sprint()
Application.ScreenUpdating = False
Dim sprintsht As Worksheet
Dim lr As Long
Set sprintsht = Sheets("Sprint Planner")
'set last row'
lr = sprintsht.Cells(Rows.Count, 2).End(xlUp).Row
'set start date and end date'
Set sd = sprintsht.Range("d5")
Set ed = sprintsht.Range("d7")
'finds tasks in WS1 and copies to sprint planner'
With Sheets("WS1")
For Each cell In .Range("D5", .Range("D" & Rows.Count).End(xlUp))
If cell.Value > sd And cell.Value < ed Then
cell.Offset(0, -3).Copy
Worksheets("WS1").Paste Destination:=sprintsht.Cells(lr + 1, 2)
cell.Offset(0, 7).Copy
Worksheets("WS1").Paste Destination:=sprintsht.Cells(lr + 1, 1)
cell.Offset(0, -2).Copy
Worksheets("WS1").Paste Destination:=sprintsht.Cells(lr + 1, 3)
cell.Offset(0, 1).Copy
Worksheets("WS1").Paste Destination:=sprintsht.Cells(lr + 1, 4)
End If
Next cell
End With
sprintsht.Select
Application.ScreenUpdating = True
End Sub
Last edited by a moderator: