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

[kpm30519]

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.

 

[jolivanes]

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?

 

[JLGWhiz]

If you always have the range to copy ending with the last row of data and you are designating the starting row manually then

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.

 

[sandy666]

try PowerQuery

M-code for result table (Table1):

[SIZE=1]// 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[/SIZE]

and small single row table (Table2) where you can determine how many last rows you want to show/copy

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]LastN[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

12​

[/td][/tr]
[/table]

example:

 

[jolivanes]

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

 

[jolivanes]

@kpm30519
Could you use any of the suggested solutions?