PDA

View Full Version : Solved: Need help exporting data based on listbox selection



sgr
04-21-2010, 09:59 PM
Hi - I am new to VBA and have worked my way to this stopping point. I've tried searching but can't seem to find what I need.

In Excel 2003, I have a worksheet that is made up of 14 rows and 16 columns. Everything is ordered in rows. So, the first column in a row is the item's name and the subsequent columns, in that row, are characteristics of that item. The second row is the second item and so on.

A user populates this worksheet (so the names of the items change from user to user as do the items characteristics) and then clicks on a command button to bring up two list boxes and some more command buttons. The first list box is populated with the names and the user can select which names to move into the second listbox.

I would like to be able to export the name and its associated characteristics (the whole row) into another worksheet based on what's in that second listbox. I can't seem to figure this last piece out. Any help or pointing in the right direction would make my head stop hurting. Thanks.

joms
04-22-2010, 03:05 AM
hi, please check attached file it might give you an idea to keep you going. :beerchug:

sgr
04-22-2010, 06:21 AM
hi, please check attached file it might give you an idea to keep you going. :beerchug:

Thanks, joms. :thumb My 2nd listbox can handle multiple selections. So I need to be able to paste multiple items. Do I just loop through the selections? Thanks again.

joms
04-22-2010, 07:12 PM
try this: change the sub listbox2_click to this code below->
just modify it to suit your needs.


Private Sub ListBox2_Click()
Dim i As Long, msg As String, irow, xrow As Integer

xrow = 1

'select the data and copy to new worksheet
With ListBox2
For i = 0 To .ListCount - 1
If .Selected(i) Then
msg = msg & .List(i)

Cells.Find(What:=msg, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Select
irow = ActiveCell.Row
Rows(irow).Select
Selection.Copy

Worksheets("Sheet2").Select
Rows(xrow).Select
ActiveSheet.Paste
Worksheets("Sheet1").Select

End If
xrow = xrow + 1
Next i

End With
End Sub

sgr
04-22-2010, 09:24 PM
Thanks, joms. Just what i needed. I can move on from here... I think. :beerchug:

joms
04-22-2010, 10:38 PM
okay, post again guys around here will be willing to help..just do your part also.. :)