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

by

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

BeforeAfter

More Related Contents:

  1. Copy filtered data to another sheet using VBA
  2. VBA macro that search for file in multiple subfolders
  3. How do I get the old value of a changed cell in Excel VBA?
  4. Force a screen update in Excel VBA
  5. Find all matches in workbook using Excel VBA
  6. Create a new sheet for each unique agent and move all data to each sheet
  7. Set Auto Filtering multiple wildcards
  8. How to make Excel VBA variables available to multiple macros?
  9. Use of symbol # (hash) in VBA Macro
  10. Excel VBA: Can’t get a match, error “Unable to get the Match property of the WorksheetFunction class”
  11. Excel VBA looping through multiple worksheets
  12. When one should use Set [e.g for SpecialCells return value]?
  13. Excel: macro to export worksheet as CSV file without leaving my current Excel sheet
  14. Is there a faster CountIF
  15. Excel VBA How to detect if something was pasted in a Worksheet
  16. Getting error Procedure too large in VBA Macros (Excel)
  17. How to add data validation to a cell using VBA
  18. How to do late binding in VBA?
  19. How to break long string to multiple lines
  20. How to copy a chart from Excel to PowerPoint?
  21. vba paste not working
  22. mac excel vba loop : from list & then export as pdf
  23. Can Advanced Filter criteria be in the VBA rather than a range?
  24. In Excel VBA, what is the way to check if the web page is fully loaded?
  25. Excel Useform: How to hide application but have icon in the taskbar
  26. Reading Web Pages using Excel VBA
  27. Excel VBA: auto click and open file from website
  28. How do I get a list of unique values from a range in Excel VBA?
  29. How to get selected value in multicolumn listbox
  30. Query my excel worksheet with VBA

Leave a Comment