Transposing to alternate columns

[ChuckDrago]

Hi everyone,

I have a database where a number of rows of a single column need to be copied (transposed) to a single row and alternating columns. For example, if my data was in A1:A10, what I need is to copy it to a different worksheet into columns A1, C1, E1, ...S1.
Before I engage into building a convoluted workaround, I wonder if anyone knows a simpler formula?
As usual, most appreciative.
Chuck

 

[JLGWhiz]

Not a formula, but a simple macro. Assuming a dynamic range in column A.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
    For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
        If c <> "" Then
            If sh2.Cells(1, 1) = "" Then
                c.Copy sh2.Cells(1, 1)
            Else
                c.Copy sh2.Cells(1, Columns.Count).End(xlToLeft).Offset(, 2)
            End If
        End If
    Next
End Sub

 

[ChuckDrago]

Not a formula, but a simple macro. Assuming a dynamic range in column A.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
    For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
        If c <> "" Then
            If sh2.Cells(1, 1) = "" Then
                c.Copy sh2.Cells(1, 1)
            Else
                c.Copy sh2.Cells(1, Columns.Count).End(xlToLeft).Offset(, 2)
            End If
        End If
    Next
End Sub

Thank you JLGWhiz! I had coded my loops combo with incremental indexes but your version is more economical, more elegant I might add. It works as I needed, it is already adopted and I remain very grateful for it.
Chuck

 

[JLGWhiz]

Thank you JLGWhiz! I had coded my loops combo with incremental indexes but your version is more economical, more elegant I might add. It works as I needed, it is already adopted and I remain very grateful for it.
Chuck

You're welcome,
regards, JLG