Excel matching based on name date and copying data to another sheet

thanks for sharing your code. I think it looks a bit more than what you need. Going off your example, if everything is formatted as such, this would be your solution:

Option Explicit
Sub SplitDateTime()

Dim mydate As String, mytime As String, mytime2 As String, i As Long, sht As Worksheet, lastrow As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")
lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

'Change headers
Range("H1:H" & lastrow).Value = Range("G1:G" & lastrow).Value
Range("G1:G" & lastrow).Value = Range("F1:F" & lastrow).Value
Range("D1").Value = "Date"
Range("E1").Value = "C/In"
Range("F1").Value = "C/Out"

'Move values around
For i = 2 To lastrow Step 2

    mydate = DateValue(Range("D" & i).Value)
    mytime = TimeValue(Range("D" & i).Value)
    mytime2 = TimeValue(Range("D" & i + 1).Value)

    Range("D" & i).Value = mydate
    Range("E" & i).Value = mytime
    Range("F" & i).Value = mytime2

Next i

'Delete excess rows
For i = lastrow To 2 Step -2
    Range("A" & i).EntireRow.Delete
Next i

'Regrab lastrow value
lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
'Change date format
Range("D2:D" & lastrow).NumberFormat = "m/d/yyyy"

End Sub


Leave a Comment