Selecting, copying and pasting the last 10 rows of a data set

[mavara]

Hi guys,

I am a recent VBA practionier so I guess the following question will be easy for most of you.
I have a large data set. I want to create a macro which selects and copies the last 10 rows of this data set (specifically the range from A to G ) and pastes them to another sheet.

Thanks in advance

 

[mumps]

Try:

Sub CopyLast10Rows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A" & LastRow - 9 & ":G" & LastRow).Copy Sheets("Sheet2").Cells(1, 1)
    Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

Try:

Sub CopyLast10Rows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A" & LastRow - 9 & ":G" & LastRow).Copy Sheets("Sheet2").Cells(1, 1)
    Application.ScreenUpdating = True
End Sub

I don't think you need those two Application.ScreenUpdating statements... copying does not display anything on the screen, so there is nothing taking place on the screen that needs to be suppressed... and since you are pasting to a non-displayed worksheet, there won't be any screen activity that will need to be suppressed there either.

 

[mavara]

Thanks,

Mumps, the code works half way through I think. It selects the entire last row of the data set but not the last 10. Also it doesnt select the range from A go G but from A to the very last column with data. I think something small is missing.

Cheers.

 

[My Aswer Is This]

This will copy your data to Sheet Named "Sheet2"

Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim Bottom As Integer
Dim Top As Integer
Bottom = Cells(Rows.Count, "A").End(xlUp).Row
Top = Bottom - 9
Range(Cells(Top, "A"), Cells(Bottom, "G")).Copy Destination:=Sheets("Sheet2").Range("A1")
Application.ScreenUpdating = True
End Sub

OK

 

[mumps]

Thank you Rick, for picking up on that.

@mavara: I tried the code on some dummy data and it worked as you requested. This line of code:

Range("A" & LastRow - 9 & ":G" & LastRow)

copies from column A to column G not the entire row and it does copy only the last 10 lines. Your actual data may be organised in a different manner. The only way to be sure is if we could see your actual workbook. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[mavara]

Mumps, My answer is this, thanks a lot guys - both solutions work perfectly fine.
Mumps, as I said I started practicing VBA recently so my ignorance made me wrongfully to conclude that the code was half way correct. Anyways all is clear now.

Thanks a lot again.

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Mumps, My answer is this, thanks a lot guys - both solutions work perfectly fine.
Mumps, as I said I started practicing VBA recently so my ignorance made me wrongfully to conclude that the code was half way correct. Anyways all is clear now.

Thanks a lot again.

 

[mumps]

Glad it worked out.