Sub CopyToAnotherPlace()
Dim Src As Range
Dim Dest As Range
Set Src = RightOfFirstBlank(Sheets("Source WorkSheet Name"), Column Number)
Set Dest = NextEmptyCell(Sheets("Destination WorkSheet Name"), Column Number)
Src.Copy Dest
End Sub
Sub CopyToAnotherPlace_2()
RightOfFirstBlank(Source Sht, Column Number).Copy NextEmptyCell(Destination Sht, Column Number)
End Sub
Function RightOfFirstBlank(Sht As WorkSheet, ColumnNumber As Long) As Range
'This uses the sheet, If you have more than a few thousand Rows before any blanks, it would
' be worth it to use an array herein.
Dim Rw As Long
With Sht
For Rw = 1 to .Cells(Rows.Count, ColumnNumber).End(xlUp).Row + 1
If .Cells(Rw, ColumnNumber) = 0
Then Set RightOfFirstBlank = .Cells(Rw, ColumnNumber + 1)
Exit For
End If
Next
End With
End Function
Function NextEmptyCell(Sht As WorkSheet, ColumnNumber As Long) As Range
With Sht
If .Cells(1, ColumnNumber) = 0 Then
Set NextEmptyCell = .Cells(1, ColumnNumber)
Else: Set NextEmptyCell = .Cells(Rows.Count, ColumnNumber).End(xlUp).Offset(1)
End If
End With
End Function