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

[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