Using Listboxes to Transfer Multiple Cells in Excel

Copper Contributor

There's probably something out there with a solution, but I've been looking for a while and I can't find anything helpful. 

Here's my problem: I want to transfer multiple cells from one Excel sheet to another by using a Listbox. I have pictures to help illustrate it, because this is difficult to picture without seeing it.


I have the Listbox already, and on the sheets, I have tables:


How can I use the Listbox from the first image to transfer the whole chart from a different sheet onto the one with the Listbox?


3 Replies


To transfer multiple cells from one Excel sheet to another using a ListBox, you can use VBA (Visual Basic for Applications) code. Here is a step-by-step guide to help you achieve this:

Assuming you have a ListBox on your first Excel sheet (Sheet1) and you want to transfer the data to another sheet (Sheet2) with tables, you can follow these steps:

  1. Open Excel and press ALT + F11 to open the VBA editor.
  2. In the VBA editor, click Insert > UserForm to insert a UserForm.
  3. Add a ListBox control to the UserForm. To do this, right-click on the UserForm in the VBA editor and choose View Code. Then, in the code window, add the following code:

Vba code:

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet2") ' Change "Sheet2" to your destination sheet name
    ' Assuming your data is in a table called "DataTable" on Sheet2
    For Each cell In ws.ListObjects("DataTable").DataBodyRange.Columns(1).Cells
        Me.ListBox1.AddItem cell.Value
    Next cell
End Sub

Private Sub TransferData_Click()
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim selectedValue As String
    ' Set the source and destination worksheets
    Set wsSource = ThisWorkbook.Sheets("Sheet2") ' Change "Sheet2" to your source sheet name
    Set wsDest = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your destination sheet name
    ' Get the selected item from the ListBox
    selectedValue = Me.ListBox1.Value
    ' Find the row in the source sheet that matches the selected value
    Set foundCell = wsSource.ListObjects("DataTable").DataBodyRange.Columns(1).Find(What:=selectedValue, LookIn:=xlValues, LookAt:=xlWhole)
    ' Check if a matching cell was found
    If Not foundCell Is Nothing Then
        ' Transfer the data to the destination sheet (assuming you have multiple columns)
        wsDest.Cells(1, 1).Value = foundCell.Value ' Change the destination cell as needed
        wsDest.Cells(1, 2).Value = foundCell.Offset(0, 1).Value ' Change the offset to select different columns
        ' You can add more lines to transfer additional columns if needed
    End If
    ' Close the UserForm
    Unload Me
End Sub

4. Close the VBA editor and return to your Excel workbook.

5. Press ALT + F8 to open the "Macro" dialog, select "UserForm_Initialize," and click "Run" to initialize the ListBox with data from your destination sheet (Sheet2).

6. Double-click the ListBox control on your UserForm to open the code window for the ListBox. Add the following code:

Vba code:


Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
End Sub

7. Save your workbook as a macro-enabled workbook with the extension .xlsm.


Now, when you double-click an item in the ListBox, it will open a UserForm that allows you to transfer the data associated with the selected item from your source sheet (Sheet2) to your destination sheet (Sheet1) or any other sheet as specified in the code.

Please note that you should customize the sheet names, table names, and destination cell references to match your actual workbook structure. The text and steps were edited with the help of AI.


My answers are voluntary and without guarantee!


Hope this will help you.

Was the answer useful? Mark them as helpful and like it!

This will help all forum participants.

It's helpful, but I was kind of hoping that it would be simple commands such as data validation and stuff, and that I wouldn't need to use VBA.


Sure. Excel is good at pulling data from another range. But you are not telling us if the data summaries start at the same location (C3) on each non-Inventory worksheet, or if there is any data on those worksheets below the summaries. I will assume the same location, no data below, and a maximum of 12 categories (can be easily increased).


You can build a reference to a cell or range as a string, and use the INDIRECT function to evaluate it:

=IF( A2="", "", FILTER( INDIRECT("'" & A2 & "'!C3:D14"), INDIRECT("'" & A2 & "'!C3:C14")>"", "nothing" ) )

(The apostrophes handle the case where the worksheet name includes a space or special character.  The FILTER function prevents rows with empty cells in the non-Inventory worksheets from being included.)


See the attached workbook for the example.