PDA

View Full Version : [SOLVED] Replacing drop downs by two multichoice listboxes to copy&paste in between sheets



dkay991
12-28-2019, 08:23 AM
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,
25708

Daniel

paulked
12-28-2019, 10:46 AM
Hi and welcome to the forum.

Try

dkay991
12-30-2019, 06:52 AM
Thank you very much! This is perfect! I adapted it in the original workbook and it works perfectly fine!

Happy New Year!

paulked
12-30-2019, 07:11 AM
You're Welcome, Happy New Year to you too! :thumb