sgr
04-28-2010, 11:29 PM
Right now I have ListBox1 that is populated from a worksheet (Sheet1) within the same workbook. I can populate ListBox2 with selections from ListBox1. If I click on the selections in ListBox2, the corresponding row on Sheet1 will be copied and pasted in the next empty row in another workbook. Ultimately, I would like to input data on Sheet1, filter the data on Sheet2 and import rows from Sheet2.
I have two questions:
1. I can get ListBox1 to populate from Sheet2, but when I select the items to copy, the rows are being copied from Sheet1. I can't get everything to populate, find and copy off of Sheet2.
2. Instead of clicking on the items in ListBox2 to initiate the copying of rows into another workbook. Can I click a command button that will copy those selections automatically (instead of clicking on each one)?
What I have so far:
Private Sub CommandButton1_Click()
Dim i As Long, msg As String
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
msg = msg & .List(i)
End If
Next i
End With
If msg = vbNullString Then
'If nothing was selected, tell user and let them try again
MsgBox "Nothing was selected! Please make a selection!"
Exit Sub
Else
ListBox2.AddItem msg
End If
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub ListBox2_Click()
Dim i As Long, msg As String, irow As Integer
Application.ScreenUpdating = False
'select the data and copy to new workbook
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
Workbooks.Open Filename:="X:\trial database.xls"
'Find last row of data
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(NextRow, 1).Select
'Paste data
Windows("trial database.xls").Activate
ActiveSheet.Paste
'Closes trial database
Windows("trial database.xls").Activate
ActiveWindow.Close True
Application.CutCopyMode = False
End If
Next i
End With
End Sub
Private Sub UserForm_Initialize()
Dim varlist As Variant
Dim lngrow As Long
Lrow = Sheets("Sheet3").Range("A65536").End(xlUp).Row
Plist = Sheets("Sheet3").Range("A1:C" & Lrow)
ListBox1.List = Plist
End Sub
I have two questions:
1. I can get ListBox1 to populate from Sheet2, but when I select the items to copy, the rows are being copied from Sheet1. I can't get everything to populate, find and copy off of Sheet2.
2. Instead of clicking on the items in ListBox2 to initiate the copying of rows into another workbook. Can I click a command button that will copy those selections automatically (instead of clicking on each one)?
What I have so far:
Private Sub CommandButton1_Click()
Dim i As Long, msg As String
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
msg = msg & .List(i)
End If
Next i
End With
If msg = vbNullString Then
'If nothing was selected, tell user and let them try again
MsgBox "Nothing was selected! Please make a selection!"
Exit Sub
Else
ListBox2.AddItem msg
End If
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub ListBox2_Click()
Dim i As Long, msg As String, irow As Integer
Application.ScreenUpdating = False
'select the data and copy to new workbook
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
Workbooks.Open Filename:="X:\trial database.xls"
'Find last row of data
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(NextRow, 1).Select
'Paste data
Windows("trial database.xls").Activate
ActiveSheet.Paste
'Closes trial database
Windows("trial database.xls").Activate
ActiveWindow.Close True
Application.CutCopyMode = False
End If
Next i
End With
End Sub
Private Sub UserForm_Initialize()
Dim varlist As Variant
Dim lngrow As Long
Lrow = Sheets("Sheet3").Range("A65536").End(xlUp).Row
Plist = Sheets("Sheet3").Range("A1:C" & Lrow)
ListBox1.List = Plist
End Sub