Copy/Paste Row from another workbook based on selection in a dynamic ComboBox

MilkyTech

Board Regular
Joined
May 17, 2015
Messages
52
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:
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
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!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Watch MrExcel Video

Forum statistics

Threads
1,129,674
Messages
5,637,731
Members
416,981
Latest member
PLonchar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top