MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copy to the first empty cell by use of a macro??????????

Posted by RONNIE on August 30, 2001 2:17 AM

How can I copy cell A1 of sheets1 to the first empty cell on sheets2 by using a macro? I want it to save, if A1 is the first empty one, to A1 and if I run it again after that to A2 and after that to A3, so on! I hope someone can help me! Thanks a lot!!!


Posted by Robb on August 30, 2001 5:44 AM


There is probably a more elegant way, but this works:

Sub III()
If Worksheets("Sheet2").Cells(1, 1) = "" Then
Worksheets("Sheet1").Cells(1, 1).Copy _
Destination:=Worksheets("Sheet2").Cells(1, 1)
ElseIf Worksheets("Sheet2").Cells(2, 1) = "" Then
Worksheets("Sheet1").Cells(1, 1).Copy _
Destination:=Worksheets("Sheet2").Cells(2, 1)
Worksheets("Sheet1").Cells(1, 1).Copy _
Destination:=Worksheets("Sheet2").Cells(1, 1).End(xlDown).Offset(1, 0)
End If
End Sub

Any help?