1 Attachment(s)
Replacing drop downs by two multichoice listboxes to copy&paste in between sheets
Hi There,
In a workbook I apply following VBA code in order to copy&paste values from one worksheet to another:
Code:
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,
Attachment 25708
Daniel
