looking for empty row to paste a range of copied cells

[Jim A]

Hi - I am trying to make a code that will copy a range ("A27:L27") on sheet 1
and then look for the next empty row on sheet 2 and paste it in range
("A27:L27") . I would also like the macro to insert a new blank row (or
insert the copied row) for the purpose of shifting existing SUM functions on
sheet 2 down. I would like those functions to be right below the
copied/pasted cells every time the macro is executed.

Thanks for any help - Jim A

 

[CurlyDave]

You don't Mention What column you want to sum

This code will copy and paste to the fist row and then sum column D

Sub CopyPasteSum()
Dim r As Range
Dim s As Range

Set s = Range("D65536").End(xlUp)

s.ClearContents

Worksheets("Sheet1").Range("A27:L27").Copy Destination:=Worksheets
("Sheet2").Range("A65536").End(xlUp).Offset(1, 0)

Set r = Range("D1", Range("D65536").End(xlUp))

Worksheets("Sheet2").Range("A65536").End(xlUp).Offset(2, 3) =
WorksheetFunction.Sum(r)

End Sub