Excel Macros recording

Question

How do I using a button (Add), copy a few cells from sheet 1 and paste into another sheet using macros? Everytime the Add button is clicked, a new row will be added. I have been trying and it keeps pasting onto the same row and not adding a new row. How do I resolve this problem? Any assistance on how should I record the macro?

Have you tried recording using the insert copied cells option instead of just pasting in the second sheet? — jswolf19

Dave Dave · Accepted Answer · 2011-03-05T12:53:19

I guess you are recording your macro by selecting the top of your range, then pressing end and then down arrow, then down arrow once more to get the first blank cell? And then of course, pasting.

If so, it's probably making a macro something like this:

Range("A1").Select
Selection.End(xlDown).Select
Range("A6").Select
ActiveSheet.Paste

The problem is obviously that pressing the down arrow is just making a new absolute selection (in my example's case, A6), rather than moving down one more cell. Simply edit the macro, changing that line to make a relative step, rather than an absolute selection. So now it will look something like this:

Range("A1").Select
Selection.End(xlDown).Select
Selection.Offset(1,0).Select
ActiveSheet.Paste

Excel Macros recording

1 Answers