Copy & Paste Date & Time

[adam]

Hi,

I’m trying to figure out a code that would copy the contents of the column “A” & ”F” from Sheet “CopyList” and paste them in columns “C” & “D” of the sheet “PasteList” with the matching text in column “E” of the sheet “PasteList..

The column labeled as “All Number” in sheet “CopyList” has ID’s in more than one row.
Let’s say for example the number “A 015652” in column “All Number” is in rows 304, 738, 753, 1185 & 1187.

When the code is run, it would copy the time and date from row 304 and paste it in row 18 of the sheet “PasteData”.

And the same would be repeated for other rows.

[HansV]

Should the code always copy/paste the first row with a given number in the All Number column, and ignore all other rows with the same number?

[adam]

Yes.

[HansV]

Please study the folllowing code, so that you can modify it if needed. I have provided comments for the most important lines.

Code: Select all

Sub CopyDatetime()
  Dim wshC As Worksheet
  Dim wshP As Worksheet
  Dim r As Long
  Dim m As Long
  Dim rngFound As Range

  Application.EnableEvents = False
  Application.ScreenUpdating = False

  Set wshC = Worksheets("CopyList")
  Set wshP = Worksheets("PasteList")

  ' Last used row in copy list
  m = wshP.Range("E" & wshP.Rows.Count).End(xlUp).Row
  ' Clear columns C and D
  wshP.Range("C11:D" & m).ClearContents
  ' Set formats
  wshP.Range("C11:C" & m).NumberFormat = "m/d/yyyy"
  wshP.Range("D11:D" & m).NumberFormat = "hh:mm:ss"
  ' Loop through cells in column E of the paste list
  For r = 11 To m
    ' Try to find value in column C of the copy list
    Set rngFound = wshC.Range("C:C").Find _
      (What:=wshP.Range("E" & r).Value, LookIn:=xlValues, LookAt:=xlWhole)
    If Not rngFound Is Nothing Then
      ' Copy date and time
      wshP.Range("C" & r).Value = rngFound.Offset(0, 3).Value
      wshP.Range("D" & r).Value = rngFound.Offset(0, -2).Value
    End If
  Next r

  Application.EnableEvents = True
  Application.ScreenUpdating = True
End Sub

[adam]

Thanks for the help Hans. I'll study the code as required.