Replacing drop downs by two multichoice listboxes to copy&paste in between sheets

[dkay991]

Hi There,


In a workbook I apply following VBA code in order to copy&paste values from one worksheet to another:

Sub search_and_extract_doublecriteria()
Application.ScreenUpdating = False
Dim datasheet As Worksheet
Dim reportsheet As Worksheet
Dim agentname As String
Dim priopartner As String
Dim finalrow As Integer
Dim i As Integer
'set variables
Set datasheet = Sheet7
Set reportsheet = Sheet1
agentname = reportsheet.Range("D2").Value
priopartner = reportsheet.Range("D4").Value
'clear old data from report sheet
reportsheet.Range("B8:T1000").ClearContents
'goto datasheet and start searching and copying
datasheet.Select
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
'loop through the rows to find the matching records
For i = 2 To finalrow
    If Cells(i, 19) = agentname And Cells(i, 11) = priopartner Then
    Range(Cells(i, 1), Cells(i, 19)).Copy
    reportsheet.Select
    Range("B1000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    datasheet.Select
    End If
    
    Next i
    
reportsheet.Select
Application.ScreenUpdating = True

End Sub

Users need to select certain variables from two dropdown menus in cells D2 and D4. Based on that the VBA code copies and then later on pastes respective values from worksheet A to worksheet B. Now, I was asked to enable the selection of multiple values within those dropdowns. My idea was to use two seperate list boxes with the mulsti select option. However, I have no idea how to link that with the existing code and most importantly how to make sure that the basic function (copy&paste) is still guaranteed.

I have attached a anonymized sample workbook including the applied code. My software is Office 365 and I have minor experience with VBA.

I'd be happy for every hint as I don't know how to solve this issue.


Greetings,
text_workook_v2.xlsm

Daniel

[paulked]

Hi and welcome to the forum.

Try

[dkay991]

Thank you very much! This is perfect! I adapted it in the original workbook and it works perfectly fine!

Happy New Year!

[paulked]

You're Welcome, Happy New Year to you too!