Adding TimeStamps to my Existing Macro

[mgao77]

Hello All,

I have an existing macro that takes data from one sheet and posts into another sheet. This process will be occuring monthly, therefore, I need an add on to the macro that will allow me to timestamp each time this happens If data from the previous month changes.

this is my current macro:

Sub Copydata()
'
' Copydata Macro
'


'
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A1").Select
Sheets("Data").Select
Sheets("Data").Select
ActiveSheet.Previous.Select
Selection.End(xlDown).Select
Range("A731").Select
Selection.End(xlUp).Select
Sheets("Data").Select
Range("A" & Sheets("Data").UsedRange.Rows.Count + 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

Can anyone help add on this timestamp please?

Thanks in advance

 

[Michael M]

Where do you want the timestamp ??

 

[mgao77]

next to where the data is pasted.. So in this case, it will be Cell BL1 through BL10000

Thanks a bunch for your support!

 

[Michael M]

Maybe something like

Sub Copydata()
Dim lr As Long
lr = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
ActiveSheet.UsedRange.Copy
Sheets("Data").Range("A" & lr + 1).PasteSpecial
lr = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
With Sheets("Data").Range("H1:H" & lr)
    .Formula = "=Now()"
    .Value = .Value
End With
End Sub

 

[Michael M]

Oops, made a typo...

Sub Copydata()
Dim lr As Long
lr = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
ActiveSheet.UsedRange.Copy
Sheets("Data").Range("A" & lr + 1).PasteSpecial
lr = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
With Sheets("Data").Range("BL1:BL" & lr)
    .Formula = "=Now()"
    .Value = .Value
End With
End Sub

 

[mgao77]

Thank you Michael!

One last question. Is it possible to make the timestamp dynamic? In other words, Since ill be doing This process every month, ill just need the timestamp to apply for that particular "import" of data. Currently, (with this code) when the data is being pasted in, the time stamp is being applied to the entire column instead of the most recent data import.

Please let me know if its possible, im sure we just have to change something in the ("BL1:BL" & lr) bit

 

[Michael M]

Ok, sorry...you did state BL1 to BL1000 !

Try

Sub Copydata()
Dim lr As Long, lr2 As Long
lr = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
ActiveSheet.UsedRange.Copy
Sheets("Data").Range("A" & lr + 1).PasteSpecial
lr2 = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
With Sheets("Data").Range("BL" & lr + 1 & ":BL" & lr2)
    .Formula = "=Now()"
    .Value = .Value
End With
End Sub