I have a combobox that populates with a dynamic list of check numbers based on the employee selected in a listbox. The check number values in the combobox change each time a different employee is selected in the listbox. These check numbers are "imported" from different worksheets in another workbook. When a particular check number is selected in the combobox, I want to find that value in the other workbook and copy/paste the entire row into this worksheet.
Also, when an employee is selected in the listbox, their employee number populates in a cell on this worksheet. That employee number is also the name of the worksheet in the other workbook where the list of check numbers (in a column) is imported from
So this is the code I use to populate the combobox:
Now that the combobox is populated with that employees check numbers, I need to write the code for the "Private Sub ComboBox1_Change()" which notates the value of the check number selected, finds that value on that employees worksheet (enumber) in the other workbook (prfile2) and copies the entire row in which that check number resides. Any help is appreciated!
Also, when an employee is selected in the listbox, their employee number populates in a cell on this worksheet. That employee number is also the name of the worksheet in the other workbook where the list of check numbers (in a column) is imported from
So this is the code I use to populate the combobox:
Code:
Dim prfile1 As StringDim prfile2 As String
Dim filepath As String
Dim checktotal As Integer
Dim checkrng As Range
Dim enumber As String
prfile1 = Worksheets("setup").Range("B10").Value
prfile2 = Worksheets("setup").Range("B7").Value
filepath = Worksheets("setup").Range("e10").Value
emunber = Worksheets("ReprintOld").Range("V3").Value
Workbooks.Open filepath & prfile2
Windows(prfile2).Activate
Sheets(emunber).Select
checktotal = Workbooks(prfile2).Worksheets(enumber).Range("AE2")
With Workbooks(prfile2).Worksheets(enumber)
Set checkrng = .Range(.Range("U5"), .Range("U" & 4 + checktotal))
checkrng.Select
Selection.Copy
End With
Windows(prfile1).Activate
Sheets("ReprintOld").Range("U2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Shapes("ComboBox1").Select
With Selection
.ListFillRange = "ReprintOld!$U$2:$U$20"
End With
Windows(prfile2).Activate
Sheets("MRegister").Select
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
Application.CutCopyMode = False
Sheets("ReprintOld").Range("A1").Select