VBA - How can I copy ONLY text from one range and paste ONLY the first three text on another sheet

[zgivod]

I have up to 6 cells with potential data coming from 6 different places. I am trying to get only the first three cells with data transferred to another sheet
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Private Sub Transfer_Data()

Sheets("sheet1").Range("A1:A6").SpecialCells(xlCellTypeConstants, 23).copy

Sheets("sheet2").Range("A1:A3").PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

End Sub</code>This is what i have i know i am missing allot. thanks

 

[JLGWhiz]

Kind of crude but it works.

Sub t()
Dim ary As Variant, i As Long, x As Long
ary = Application.Transpose(Range("A1:A6"))
    For i = 1 To UBound(ary)
        If ary(i) <> "" Then
            x = x + 1
            Sheets(2).Range("A" & x) = ary(i)
            If x = 3 Then Exit For
        End If
    Next
End Sub

 

[Rick Rothstein]

Kind of crude but it works.

Sub t()
Dim ary As Variant, i As Long, x As Long
ary = Application.Transpose(Range("A1:A6"))
    For i = 1 To UBound(ary)
        If ary(i) <> "" Then
            x = x + 1
            Sheets(2).Range("A" & x) = ary(i)
            If x = 3 Then Exit For
        End If
    Next
End Sub

Assuming the pipe symbol (|) will never be found in the text of any cell, here is another way to write this macro...

[table="width: 500"]
[tr]
	[td]Sub FirstThreeTextCells()
  Dim Combo As String
  Combo = Replace(Replace(Join([TRANSPOSE(A1:A6)], "|"), "|||", "|"), "||", "|")
  Sheets(2).[A1:A3] = Application.Transpose(Split(Mid(Combo, 1 - (Left(Combo, 1) = "|")), "|"))
End Sub[/td]
[/tr]
[/table]