CopyPaste data depending on ListBox Selections


I have been trying to figure this out on my own before asking on a forum - silly me, I thought I would be able to do it by myself. Please help!

I have 2 files: Database.xls and Sample Facilities Systems.xls

The Database workbook contains info that will be changed/updated regularly and the Sample Facilties Systems workbook will act as the interface for retrieving info from the Database workbook.

I have code that allows user to click on a cell that opens a listbox (which works fine) Depending on which Category the user chooses/is interested in on the Sample Facilities Systems.xls, the ListBox is populated by the values(in this case, Topics of interest) in the first row of the corresponding worksheet in Database.xls:

What I DON'T know how to do is how to paste the Details listed under each Topic in Database.xls to show up along with the Topics that were selected from the ListBox onto Sheet1 of Sample Facilities Systems.xls (under the same Topic column)

Current (and error-filled) code to paste all Selections from Listbox and Details of Selections (from Database.xls) to Sheet1 of Sample Facilties Systems.xls:

Private Sub OK_Click()
Dim i As Long, msg As String
Dim c As Long, d As Long, e As Long
Dim Counter As Integer

c = 0
e = ActiveCell.Column
d = 0

With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
'Paste Selection
ActiveCell.Offset(2, 0) = .List(i)
'Get Details of ListBox Selection from
'Database.xls to paste in Sample Facilties Systems.xls
' Arbitrary large number 30 chosen to ensure all
' Details are included/listed
For Counter = 1 To 30
Workbooks("Database.xls").Sheets(e).Cells(Counter, i + 1).Copy
ActiveSheet.Cells(0, e).End(xlDown).Offset(1, 0).Paste
Application.CutCopyMode = False
Next Counter
End If
Next i
End With
Unload Me
End Sub

Code to populate ListBox with Topic info from Database.xls:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 1 Then
' "1 to #" - # depends on # of items in Row 1
' of Sheet 1 of Database.xls
For Column = 1 To 5
UserForm1.ListBox1.AddItem Workbooks("Database.xls"). _
Sheets(1).Cells(1, Column)
Next Column
ElseIf Target.Row = 2 And Target.Column = 2 Then
'"1 to #" - # depends on # of items in Row 1
'of Sheet 2 Database.xls
For Column = 1 To 2
UserForm1.ListBox1.AddItem Workbooks("Database.xls"). _
Sheets(2).Cells(1, Column)
Next Column
ElseIf Target.Row = 2 And Target.Column = 3 Then
' "1 to #" - # depends on # of items in Row 1
' of Sheet 3 Database.xls
For Column = 1 To 3
UserForm1.ListBox1.AddItem Workbooks("Database.xls"). _
Sheets(3).Cells(1, Column)
Next Column
End If
End Sub


Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!