How to copy all values in a multi column listbox to another worksheet RRS feed

  • Question

  • My listbox has 7 columns and contain the values from another worksheet which is already filtered. I would like to create an "Export" button wherein I could copy all the values from the multi column listbox into another workbook.

    I found the 2 examples below from

    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

    For Example 1, at first, I planned to modify "TheRange" into another worksheet and copy the values to another workbook. However, I'm unable to change the destination of "TheRange" using the code below

        Dim TheArray As Variant    

    <span style="background-image:initial;background-attachment:initial;background-size:initial;background-origin:initial;background-clip:i

    Tuesday, July 22, 2014 5:40 AM


  • If I am interpreting your question correctly then the following will copy the ListBox List to a worksheet in another workbook.

    Private Sub CommandButton1_Click()
        Dim wBook As Workbook
        Dim wSht As Worksheet
        Dim TheArray As Variant
        Dim TheRange As Range
        Dim strDestFileName As String
        'Use following 2 lines if destination workbook already open
        'strDestFileName = "My Destination Workbook.xlsx"
        'Set wBook = Workbooks(strDestFileName)
        'If "My Destination Workbook.xlsx" NOT already open then _
         use the following 2 lines. Note requires a full path.
        strDestFileName = ThisWorkbook.Path & "\" & "My Destination Workbook.xlsx"
        Set wBook = Workbooks.Open(strDestFileName)
        Set wSht = wBook.Worksheets("Sheet1")
        TheArray = ListBox1.List
        With wSht
            'The following resizes the destination range from one cell to the _
             same size as the array. The array is zero based to match the _
             ListBox List which is also a zero based array. Hense, adding _
             1 to both the Ubound values (both dimensions of the array.)
            Set TheRange = .Cells(1, 1) _
                    .Resize(UBound(TheArray, 1) + 1, UBound(TheArray, 2) + 1)
            TheRange = TheArray
        End With
        'Following closes the destination workbook
        wBook.Close SaveChanges:=True   'Optional line of code

    End Sub

    Regards, OssieMac

    Tuesday, July 22, 2014 11:47 AM