VBA add date automatically when row is copied from sheet to another

[lunatu]

Hi,

Didn't find an answer from here so lets ask this way...
Im copying data automatically from sheet1 to sheet2 and I would like there to be automatically current date in first column of the sheet2 when data is transferred.
Any idea how to solve this?

Thanks in advance!

 

[offthelip]

just add this to the code doing the copying:

Worksheets("Sheet2").Range("a1") = Date

 

[lunatu]

Thanks! Seems to be working, but one (silly) question still: how do I set the range correctly when I want the date be always at same row where the data is pasted (= next empty row)?

 

[offthelip]

try this:

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Sheet2").Range("a" & lastrow + 1) = Date

 

[lunatu]

Thank you, but still struggling with this... this code is working but it adds the date one row above, example the data is pasted to row 3 but the date is in row 2.

 

[offthelip]

what is your code for copying and pasting the data? If you post it here I then have chance to see what that is doing

 

[lunatu]

This code Im using for copy+paste:

Sub CopyCell()

Dim LR As Long, i As Long
With Sheets("Sheet1")
LR = .Range("H" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If .Range("H" & i).Value = "Sales Person 1" And .Range("J" & i).Value = "Sold" Then
.Range("A" & i).Resize(1, 9).Copy Destination:=Sheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End With
End Sub

 

[offthelip]

try trhis:

Sub CopyCell()

Dim LR As Long, i As Long
With Sheets("Sheet1")
LR = .Range("H" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If .Range("H" & i).Value = "Sales Person 1" And .Range("J" & i).Value = "Sold" Then
dlr = Sheets("Sheet2").Cells(Rows.Count, "D").End(xlUp).Row + 1
.Range("A" & i).Resize(1, 9).Copy Destination:=Sheets("Sheet2").Range("D" & dlr)
Sheets("Sheet2").Range("A" & dlr) = Date
End If
Next i
End With
End Sub

 

[lunatu]

BIG thanks - this is working perfectly!