Results 1 to 9 of 9

Thread: VBA To Extract Latest X Number Of Rows From A Range and Paste To Another

  1. #1
    Member
    Join Date
    Apr 2014
    Posts
    45
    Rep Power
    0

    VBA To Extract Latest X Number Of Rows From A Range and Paste To Another

    I have a range of data from A1:F5000, what is the code to extract the last/newest 500 rows of the data and paste into H:M

  2. #2
    Junior Member
    Join Date
    Jan 2012
    Posts
    10
    Rep Power
    0
    You could try this
    Code:
    Range("A" & Rows.Count).End(xlUp)(-498).Resize(500,6).Copy Range(H1")

  3. #3
    Member
    Join Date
    Apr 2014
    Posts
    45
    Rep Power
    0
    the code shows an error when the no. of rows in the range is lesser than 500 rows, how to correct it, thank you

  4. #4
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Why don't you try to come up with a solution yourself ?
    The basic ingredients have been provided to you.
    If you read the code and analyse it it's not complicated.

  5. #5
    Member
    Join Date
    Apr 2014
    Posts
    45
    Rep Power
    0
    problem solved, thank you
    Last edited by mrprofit; 04-11-2014 at 03:32 PM.

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    mrprofit, for posterity, it would be great if you could share your solution. Here's my solution

    Code:
        With Range("A" & Rows.Count).End(xlUp)
            .Cells(Application.Max(-498, 2 - .Row)).Resize(Application.Min(500, .Row), 6).Copy Range("H1")
        End With
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Member
    Join Date
    Apr 2014
    Posts
    45
    Rep Power
    0
    My solution,

    Code:
    If Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row >= 500 Then
           Range("A" & Rows.Count).End(xlUp)(-498).Resize(500, 6).Select
        Else
     Range("A1:F500").Select
    .....
    End IF
    Last edited by mrprofit; 04-11-2014 at 05:55 PM.

  8. #8
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Code:
    Sub M_snb()
        Range("A1:F500").Offset(Abs((Columns(1).SpecialCells(2).Count > 500)) * Columns(1).SpecialCells(2).Count).copy cells(1,8)
    End Sub

  9. #9
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    This single code line should also work...

    Code:
    Cells(Application.Max(1, Cells(Rows.Count, "A").End(xlUp).Row - 499), "A").Resize(500, 6).Copy Range("H1")
    Last edited by bakerman; 04-14-2014 at 05:22 AM. Reason: Code tags added for readability

Similar Threads

  1. Copy/Paste Excel Range/Chart into Powerpoint VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 03-13-2014, 02:59 PM
  2. Replies: 2
    Last Post: 03-08-2014, 02:49 AM
  3. Replies: 18
    Last Post: 02-12-2014, 10:47 AM
  4. VBA To Extract Certain Rows From A Text File
    By Bogdan in forum Excel Help
    Replies: 4
    Last Post: 08-31-2013, 06:57 PM
  5. Macro Copy Columns and Paste in rows.
    By TommyKris in forum Excel Help
    Replies: 3
    Last Post: 03-06-2013, 02:36 PM

Posting Permissions

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