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

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

You could try this

Code:

---

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

---

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

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.

problem solved, thank you

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

Code:

---

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

---

My solution, :)

Code:

---

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

---

Code:

---

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

---

This single code line should also work...

Code:

---

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

---