How to Paste a Single Column of ListBox Values to Sheet Across a Row

[RunTime91]

Greetings All,

I have a MultiSelect ListBox with a single column of values that I would like to transfer to a sheet across a row

The following code transfers the selected values in the listbox to a single column.

Private Sub CommandButton2_Click()

Dim i As Log

For i = 0 To ListBox3.ListCount - 1

If Me.ListBox3.Selected(i) = True Then
  ThisWorkbook.Sheets("Sheet5").Range("C500").End(xlUp).Offset(1, 0) = Me.ListBox3.List(i, 0)
End If

Next i

End Sub

E.G., if the user selects 4 values in the listbox then clicks the commandbutton for the above code the values are transferred to C2-C5

What I'm needing is for those 4 values to be transferred to C2, D2, E2, F2

I'm currently trying to use copy/transpose, but thus far that is not working.

Thank You, Thank You for any help

RT 91

 

[Akuini]

What I'm needing is for those 4 values to be transferred to C2, D2, E2, F2

Try:

Private Sub CommandButton2_Click()

Dim i As Long, n As Long
Dim c As Range

Set c = ThisWorkbook.Sheets("Sheet5").Range("C2")

For i = 0 To ListBox3.ListCount - 1

    If Me.ListBox3.Selected(i) = True Then
      c.Offset(, n) = Me.ListBox3.List(i, 0)
      n = n + 1
    End If

Next i

End Sub

 

[Trebor76]

Hi there,

Try this:

Option Explicit
Private Sub CommandButton2_Click()

    Dim i As Long, j As Long
    
    j = ThisWorkbook.Sheets("Sheet5").Cells(Rows.Count, "C").End(xlUp).Row + 1

    For i = 0 To ListBox3.ListCount - 1
        If Me.ListBox3.Selected(i) = True Then
            ThisWorkbook.Sheets("Sheet5").Range("C" & j).Offset(0, i) = Me.ListBox3.List(i, 0)
        End If
    Next i

End Sub

Regards,

Robert

 

[RunTime91]

Akuini & Robert ~ Cannot thank either of you enough!

I knew there had to be a better way than Copy/PasteSpecial/Transpose...

And now I know there are at least 2 'MUCH' better ways!!

Thank You Both, Very Much!!

RT91

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.