VBA Copy Last 30 Days – Paste Elsewhere (2003-SP2)

Topic

New Reply

Hello all,

I have a list of data collected daily. Latest data is manually appended to list below last full row.

After data is manually entered, I want to develop a macro that, upon execution, will:

1. Select the range that includes data of the last 30 days, that is current cell and previous 29 rows. The list is in columns A through K.
2. Copy the selected range.
3. Paste the clipboard into range always starting at S3 in the same worksheet.
4. Make S3 the active cell, in effect, de-selecting the source and the destination ranges.

I’ve attached a sample workbook that includes my faltered VBA first attempt.

Thanks so much,
G’Day,
Rich

Reply | Quote

Replies

Try this:

Sub copy_last_thirty_days()
Range(“A3”).End(xlDown).Offset(-29, 0).Resize(30, 11).Copy _
Destination:=Range(“S3”)
End Sub

The code doesn’t select cells, so there is no need to select a cell at the end of the macro.

Reply | Quote

Hans,

As always, you’re a genius. It meets my needs exactly.

Can you explain “Range(“A3″).End(xlDown)”?

Thanks so much,
Your help was a great Birthday present (Mar. 10th).
Cheers,
Rich

Reply | Quote

Happy Birthday!

End(xlDown) is the VBA equivalent of pressing End then down-arrow, except that it doesn’t select a cell, but returns a reference to the cell instead. Range(“A3”).End(xlDown) returns a reference of the last filled cell if you move from A3 downswards.

Reply | Quote