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

[jolivanes]

You could try this

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