looking for an empty row to paste copied range of cells

[Jim A]

Hi - I am trying to get a range ("A27:L27") in sheet 1 to be copied and then
pasted in range ("A27:L27") in sheet 2 at the next empty row. I would like
the macro to insert a new row in sheet 2 so it will push functions only one
row lower each time the macro is executed.

 

[Gord Dibben]

Your description does seem to match your requirements.

You want to place A27:L27 in sheet2 A27:L27 or at next empty row?

This macro will copy to A27:L27, insert a row and push all down leaving row
27 blank to receive next copy.

Sub moveit()
Dim rng1 As Range
Set rng1 = Sheets("Sheet6").Range("A27")
ActiveSheet.Range("A27:L27").Copy Destination:=rng1
rng1.Offset(-1, 0).EntireRow.Insert
End Sub


Gord Dibben MS Excel MVP

 

[Jim A]

Thanks for the quick response - I see that this will work, but my sheet 2
will usually have preexisting data on it (in which I would like to add the
same kind of data too your kind of code below).

It looks like this code will paste over existing data?

Again, thanks - Jim A

 

[Jim A]

Sorry - on sheet 2 the macro needs to copy to the next empty row - not on row
27. Thanks for your help - Jim A

 

[Gord Dibben]

No it does not.

Did you try it?


Gord

 

[Gord Dibben]

OK, next empty row on sheet2

Sub moveit()
Dim rng1 As Range
Set rng1 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
ActiveSheet.Range("A27:L27").Copy Destination:=rng1
End Sub


Gord

 

[Jim A]

Ah...I shoulda tried it first. I guess I am confused on how your
"rng1.Offset(-1, 0).EntireRow.Insert" works.
I think I see now and thanks - Jim A

 

[Gord Dibben]

You're right. I'm wrong.

Apologies.

Time for a long rest.


Gord