I'm not sure I understand exactly what you want to do,
but the following will literally copy every filled cell
on the active sheet into column "A" of "Sheet2" (note
that the third and fourth lines should be written on one
line in the module):
For Each cell In ActiveSheet.UsedRange.Cells
If cell.FormulaR1C1 <> "" Then
cell.Copy Destination:=Sheets("Sheet2").Range
("A65536").End(xlUp).Offset(1, 0)
End If
Next cell
Hope this helps...
