none
Copy and paste items form a listbox to a sheet RRS feed

  • Question

  • I would like to copy every item in the listbox and paste them in the excel sheet. Here's my code. It reads "error 1004" when I lance it...

    Could anyone tell me where went wrong?

    Thank you!

    Private Sub ListBox1_Click()


    Dim i As Integer

    For i = 0 To ListBox1.ListCount - 1


    If ListBox1.Selected(i) = True Then

    Selection.Copy

    Worksheets("All sales").Cells(i, 1).Paste


    End If

    Next i

     
    Saturday, October 12, 2013 7:15 PM

Answers

  • Hi,

    You can transter the listbox items in two ways:

    1. Copy the listbox contents to array then copy the array to Excel range

    2. Looping the listbox elements and transfer them to Excel cells.

    You can trigger the action by pressing a command button for example

    Example 1:

    Sub CopyFromArray()

    Dim TheArray As Variant
    Dim TheRange As Range

    TheArray = ListBox1.List


    Set TheRange = Range(Cells(1, 1), Cells(UBound(TheArray) + 1, 1))

    TheRange = TheArray

    End Sub

    Example 2:

    Sub CopyBYLoop()

    Dim i As Long

    For i = 0 To ListBox1.ListCount - 1
        Cells(i + 1, 1) = ListBox1.List(i)
    Next i


    End Sub


    Guy Zommer

    Sunday, October 13, 2013 5:20 AM

All replies

  • Hi,

    You can transter the listbox items in two ways:

    1. Copy the listbox contents to array then copy the array to Excel range

    2. Looping the listbox elements and transfer them to Excel cells.

    You can trigger the action by pressing a command button for example

    Example 1:

    Sub CopyFromArray()

    Dim TheArray As Variant
    Dim TheRange As Range

    TheArray = ListBox1.List


    Set TheRange = Range(Cells(1, 1), Cells(UBound(TheArray) + 1, 1))

    TheRange = TheArray

    End Sub

    Example 2:

    Sub CopyBYLoop()

    Dim i As Long

    For i = 0 To ListBox1.ListCount - 1
        Cells(i + 1, 1) = ListBox1.List(i)
    Next i


    End Sub


    Guy Zommer

    Sunday, October 13, 2013 5:20 AM
  • Hi,

    Is it OK?


    Guy Zommer

    Wednesday, October 16, 2013 4:58 AM