Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

get the last 10nd row from sheet to another

0

Hi

I try copying the last of ten rows from sheet1 to sheet2 

this code doesn't work, may anybody help,please?


Sub Copy_Lastten_Rows()

Dim sht1            As Worksheet
Dim sht2 As Worksheet
Dim LastRow         As Long
Dim Last10Rows       As Long
Set sht1 = ThisWorkbook.Sheets("Sheet1")
Set sht2 = ThisWorkbook.Sheets("Sheet2")
LastRow = sht1.Range("E" & sht1.Rows.Count).End(xlUp).Row
 Last10Rows = sht2.Range("A" & sht2.Rows.Count).End(xlUp).Row
 sht2.Range("A" & Last10Rows).Copy sht1.Range("A" & LastRow - 9 & ":E" & LastRow - 9)

End Sub

I put the expected result in second sheet.

Answer
Discuss

Answers

0
Selected Answer

Hello again speed,

You are close but there are a couple of changes needed. You say in your post you want to copy the last 10 rows on Sheet1 to Sheet2 but what you have written, your code copies from Sheet2 to Sheet1 - opposite of what you want.

You don't indicate if you want these 10 rows to replace the existing Sheet2 rows or to copy these 10 rows below the existing. So I have written code for both options.

Sub Copy_Last10_Rows_Replace()

Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim LastRow As Long
Dim Last10Rows As Long
Set sht1 = ThisWorkbook.Sheets("Sheet1")
Set sht2 = ThisWorkbook.Sheets("Sheet2")

LastRow = sht1.Range("A" & Rows.Count).End(xlUp).Row

Last10Rows = sht2.Range("A" & Rows.Count).End(xlUp).Row

' copy last 10 rows from sht1 to sht2 and replace existing on sht2
sht1.Range(LastRow - 9 & ":" & LastRow).Copy sht2.Range("2:11")

End Sub

If you want to add the sht1 data to the existing sht2 data then use the following:

Sub Copy_Last10_Rows_Add()

Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim LastRow As Long
Dim Last10Rows As Long
Set sht1 = ThisWorkbook.Sheets("Sheet1")
Set sht2 = ThisWorkbook.Sheets("Sheet2")

LastRow = sht1.Range("A" & Rows.Count).End(xlUp).Row

Last10Rows = sht2.Range("A" & Rows.Count).End(xlUp).Row

' copy last 10 rows from sht1 to sht2 below existing data on sht2
sht1.Range(LastRow - 9 & ":" & LastRow).Copy sht2.Range(Last10Rows + 1 & ":" & Last10Rows + 1)

End Sub

If this solves your issue  please mark my answer as Selected.

Cheers   :-)

Discuss

Discussion

Thanks Willie
but I don't want repeat copying to the bottom when every time run the macro .
I try adding this line as bold but doesn't work correctly 
sht2.Range("A2").CurrentRegion.ClearContents
sht1.Range(LastRow - 9 & ":" & LastRow).Copy sht2.Range(Last10Rows + 1 & ":" & Last10Rows + 1)

any suggestion to fix it please?
speed (rep: 42) Feb 12, '24 at 5:54 am
Hi speed,

So you want to overwrite the existing Sheet2 data with the new data from Sheet1. I provided this in my answer.  It is the line:
' copy last 10 rows from sht1 to sht2 and replace existing on sht2
sht1.Range(LastRow - 9 & ":" & LastRow).Copy sht2.Range("2:11")

The line that follows this will add the Sheet1 data below the exisiting Sheet2 data, so just delete the line:
' copy last 10 rows from sht1 to sht2 below existing data on sht2
sht1.Range(LastRow - 9 & ":" & LastRow).Copy sht2.Range(Last10Rows + 1 & ":" & Last10Rows + 1)

Cheers   :-)
WillieD24 (rep: 637) Feb 12, '24 at 10:03 am
sorry just I used second suggetion.
thank you so much for your support  me.
speed (rep: 42) Feb 12, '24 at 1:57 pm
I have edited my original answer to show the two macros without the need for you to edit my original code to suit your needs.
WillieD24 (rep: 637) Feb 12, '24 at 11:16 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login