I can see you are very new to this and that is fine, we all were once! Using recorded macros is a good way to see how excel views what you are doing at the time but it is extremely inefficient compared to what it could be. As Ron has mentioned, select really is not a friend of efficient code. For example, your first four lines could be rewritten into one line as:
Sheets("Total").Range("D6923").End(xlDown).copy
However even this isn't the best way. I'm going to assume that you are working from the top of your sheet to the bottom and answer your question based on what I think you are trying to do. I'm also assuming that your sheet called Timeline is sheet 1 and your sheet called Total is sheet 2. Within total I am assuming that any number of entries could be there rather than just the two shown in the three examples given.
Sub ExampleCode()
'Variables, you can create and store things in VBA to make life easier for you
Dim Wb as Workbook 'This is the workbook you are using
Dim wsTimeline as Worksheet 'This is your worksheet called Timeline
Dim wsTotal as Worksheet 'This is your worksheet called as Total
Const rMin as byte = 5 'This is where the loop will start, I'm assuming row 5. As _
this won't change throughout the code and we know it at the _
start it can be a constant
Dim rMax as long 'This will be the last row in your loop
Dim r as long 'This will be how your loop knows which row to use
Dim timelineRow as long 'This will be the row that the data is pasted in Timeline
Dim timelineLastRow as Long 'This is the last row of data in your timeline sheet
Set Wb = Thisworkbook 'Your whole workbook is now stored in the variable Wb
Set wsTimeline = Wb.Sheets("Timeline") 'As the workbook was stored in Wb we can use it as _
shorthand here. Now the sheet Timeline is in wsTimeline
Set wsTotal = Wb.Sheets("Total") 'Same as above, this sheet is now stored
rMax = wsTotal.Cells(Rows.Count, 1).End(xlUp).Row 'This is the equivalent of starting at the _
bottom row in column A and pressing _
Ctrl+Up. This takes you to the last _
row of data in column A. …(Rows.Count, 2)… _
would be column B etc.
timelineLastRow = wsTimeline.Cells(Rows.Count, 1).End(xlUp).Row
'This is the bit where you start to loop, the line below basically says "Do the code in this _
loop for every value between rMin and rMax, each time make 'r' that value (r for row!)
With wsTotal 'Means that anything below starting with '.' will _
be the same as 'wsTotal.'
For r = rMin To rMax
'Ensure working on a line with data
If .Cells(r, 1) = "" Then
r = .Cells(r, 1).end(xlDown).row
If r > rMax Then
End With 'Closes the With statement above as no longer needed.
Exit For 'Exits the loop as we have ended up beyond rMax
End if
End if
'This will look for the person in wsTimeline and if they aren't there then add them
If IsError(Application.Match(.Cells(r, 1), wsTimeline.Range("A3:A" & timelineLastRow), 0)) Then
wsTimeline.Cells(timelineLastRow + 1, 1) = wsTotal.Cells(r, 1)
timelineRow = timeLineLastRow + 1
timelineLastRow = timelineRow
Else
timelineRow = Application.Match(.Cells(r, 1), wsTimeline.Range("A3:A" & timelineLastRow), 0)
End If
'I'm assuming that all records in 'Total' are chronologically ascending with no gaps between _
each row for a single person.
wsTimeline.Cells(timelineRow, 3) = .Cells(r + 2, 4)
If .cells(r + 3, 4) <> "" then
wsTimeline.Cells(timelineRow, 4) = .Cells(r + 2, 4).End(xlDown)
Else
wsTimeline.Cells(timelineRow, 4) = .Cells(r + 2, 4).End(xlDown)
End If
'Now that the data has been brought across from Total to Timeline we can move on to _
the next row.
Next r 'This will add one to the value stored in r and start the code again where _
the loop started
End With
'The loop has now ended having gone through every row in your worksheet called Total.
End Sub
