Just replace "a" with "b" for col. reference

http://www.mrexcel.com/forum/excel-q...ic-data-2.html
Sub test()
    Dim myAreas As Areas, myArea As Range, n As Long
    With Sheets("sheet1")
        With .Range("b1", .Range("b" & Rows.Count).End(xlUp))
            On Error Resume Next
            Set myAreas = .SpecialCells(2).Areas
            On Error GoTo 0
            If myAreas Is Nothing Then Exit Sub
            For Each myArea In myAreas
                n = n + 1
                If myArea.Rows.Count > 1 Then
                    Sheets("sheet2").Cells(n, 1).Resize(, myArea.Rows.Count).Value = _
                    Evaluate("transpose(sheet1!" & myArea.Address & ")")
                Else
                    Sheets("sheet2").Cells(n, 1).Value = myArea.Value
                End If
            Next
        End With
    End With
End Sub