Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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. <LI>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.
      <LI>Copy the selected range.
      <LI>Paste the clipboard into range always starting at S3 in the same worksheet.
      <LI>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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Copy Last 30 Days - Paste Elsewhere (2003-

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Copy Last 30 Days - Paste Elsewhere (2003-

    Happy Birthday! <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •