Results 1 to 6 of 6

Thread: Need to copy last n rows of range to 2nd worksheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2018
    Posts
    17
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Need to copy last n rows of range to 2nd worksheet

    Hi--

    Major senior moment-- I need a macro to copy the last n rows of a range (A:I) to another worksheet. It needs to be dynamic (might be last 10, might be last 27, etc.) On the worksheet the data goes to, it should always paste to A3 so it will update/ overwrite the previous data.

    Ex:
    Today, I have data in Sheet1(A3:I300). I need Sheet1(A290:I300) copied to Sheet2(A3).
    Tomorrow, I will have data in Sheet1(A3:I425), but I need Sheet1(A415:I425) copied to Sheet2(A3).

    Hope this makes sense!

    Thanks in advance.

  2. #2
    Board Regular
    Join Date
    Sep 2004
    Posts
    1,377
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to copy last n rows of range to 2nd worksheet

    Am I reading you right that you want the last 11 used rows copied and pasted to another sheet?
    First you say that it might be a different amount and after that your examples show just 11 on both occasions.
    How are we to know how many?
    Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Politicians combine theory and practice: nothing works and they donít know why.

  3. #3
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    11,031
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Need to copy last n rows of range to 2nd worksheet

    If you always have the range to copy ending with the last row of data and you are designating the starting row manually then
    Code:
    With ActiveSheet
     Application.DisplayAlerts = False
     .Range(.Cells(ActiveCell.Row, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 9).Copy Sheets(2).Range("A3")
     Application.DisplayAlerts = True
    End With
    If you are finding the starting row by code then the criteria would determine how the copy range is formed.
    Last edited by JLGWhiz; Jul 5th, 2019 at 05:21 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,792
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Need to copy last n rows of range to 2nd worksheet

    try PowerQuery

    M-code for result table (Table1):

    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Counter = Table.FirstValue(Excel.CurrentWorkbook(){[Name="Table2"]}[Content]),
        Type = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ID", type text}, {"Name", type text}, {"Company", type text}, {"Country", type text}, {"City", type text}, {"Sales", Int64.Type}}),
        LastN = Table.LastN(Type,Counter)
    in
        LastN
    and small single row table (Table2) where you can determine how many last rows you want to show/copy

    LastN
    12


    example:

    Last edited by sandy666; Jul 5th, 2019 at 05:49 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    Board Regular
    Join Date
    Sep 2004
    Posts
    1,377
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to copy last n rows of range to 2nd worksheet

    Code:
    Sub Maybe()
    Dim i As Long, sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = Worksheets("Sheet1")
    Set sh2 = Worksheets("Sheet2")
    sh2.UsedRange.Offset(2).ClearContents
    i = InputBox("How many rows do want to copy?", "Amount of rows.")
        With sh1
            .Cells(.Rows.Count, 1).End(xlUp).Offset(-(i-1)).Resize(i, 9).Copy sh2.Range("A3")
        End With
    End Sub
    Last edited by jolivanes; Jul 5th, 2019 at 05:51 PM. Reason: fix slip of the finger
    Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Politicians combine theory and practice: nothing works and they donít know why.

  6. #6
    Board Regular
    Join Date
    Sep 2004
    Posts
    1,377
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to copy last n rows of range to 2nd worksheet

    @kpm30519
    Could you use any of the suggested solutions?
    Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Politicians combine theory and practice: nothing works and they donít know why.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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