Sep 27 2023 05:54 AM
Sep 27 2023 05:54 AM
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?
Sep 27 2023 09:54 AM
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:
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:
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) UserForm1.Show 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.
Sep 29 2023 04:45 AM
Sep 29 2023 03:38 PM
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.