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

[mrprofit]

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

[jolivanes]

You could try this

Range("A" & Rows.Count).End(xlUp)(-498).Resize(500,6).Copy Range(H1")

[mrprofit]

the code shows an error when the no. of rows in the range is lesser than 500 rows, how to correct it, thank you

[snb]

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.

[mrprofit]

problem solved, thank you

[Excel Fox]

mrprofit, for posterity, it would be great if you could share your solution. Here's my solution



With Range("A" & Rows.Count).End(xlUp)
.Cells(Application.Max(-498, 2 - .Row)).Resize(Application.Min(500, .Row), 6).Copy Range("H1")
End With

[mrprofit]

My solution, :)


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

[snb]

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

[Rick Rothstein]

This single code line should also work...


Cells(Application.Max(1, Cells(Rows.Count, "A").End(xlUp).Row - 499), "A").Resize(500, 6).Copy Range("H1")