How to apply the resize property to an active cell

[myespooir]

Hi there,

I would like to ask for help on how to resize based on the active cell location.

I need the macro to copy the last 20 rows (Jan) of my existing data to generate the same formulas for the next Month. This is the code I recorded/edited.

    Range("A2").Select
    Selection.End(xlDown).Select
    ActiveCell.Resize(-21, 6).Select
    ActiveCell.Activate
    Selection.Copy
    Range("A2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste

However, the problem seems to be that i cannot resize the activecells. Could you let me know how to rewrite the macro?

Thanks a million!

Myespooir

 

[RoryA]

You can't resize to a negative number. Try this:

    Range("A2").End(xlDown).offset(-19, 0).Resize(20, 6).Copy Destination:=Range("A2").End(xlDown).Offset(1, 0)

 

[myespooir]

Thanks a million, Rory!!!!
It works perfectly!!!