Help Create an Effective Marco

[claire]

Trying to write a macro which will copy a row from the top of a list
and then scroll down to the next empty row and paste.

I have tried Ctl C then Ctl down arrow, then down arrow, which works i
you are doing it manually but when automating with a macro does no
recognise that it needs to find the next available empty row befor
pasting.

Please HELP

 

[Dave Smith]

I used the "Relative reference" button to create this macro:

Sub CopyListTop()
'
' CopyListTop Macro
' Macro recorded 1/8/2004 by dave
'

'
Range("A1:F1").Select
selection.Copy
selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

HTH

Dave

 

[Gord Dibben]

Claire

Not sure if you mean "first" blank row or "first" blank row at bottom? Could
be different.

To copy row 1 to first empty row at bottom of column A use this.

Sub Copyit()
Range("1:1").Copy Destination:= _
ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
End Sub

To copy row 1 to first blank row in Column A use this.

Sub copy_row()
Range("1:1").Copy Destination:= _
ActiveSheet.Cells.End(xlDown) _
.Offset(1, 0)
End Sub

Gord Dibben Excel MVP