copy the cell value with matching multiple criteria from two sheets

Question

I am quite new or intermediate learner to vba programing and came up with a task to copy a cell value from workbook1 of sheet1 to workbook2 of sheet1 by matching up with multiple criteria and copy the Sort reference(workbook1) column cell value to Notes And Assumption(workbook2) cells.

Workbook1 Column Workbook2 Column Functional Group matches with-------> Primary Skills

LOB matches with-------> Service Line

Region/Country matches with-------> Work Required Country

Sort Reference(Workbook1)-------------------> Notes And Assumptions(Workbook2) (Note:- Matching the criteria and copy the corresponding row Sort Reference value to column Notes and Assumptions)

Destination Workbook2 Column to match Criteria to match from workbook2

Source Workbook1 columns are Criteria to match from workbook1

Demo of Output Output of workbook2 in Notes And Assumptions Column Note:- Output should be like this as given in Image and if not data found leave blank

I tried below but not getting the result because if the criteria gets matched and values get pasted in a particular column then that matched row should not repeat again and it starts from the next row to match. Because of this my values get override.

   Sub sortref()

Dim wbk, wbk1 As Workbook
Dim sht, sht1 As Worksheet
Dim i, j, k, n, m As Long
Dim FGroup, PSkills, RegCoun, WrCoun, MRCode, RCode As String

Application.DisplayAlerts = False

Set wbk = Workbooks.Open(Filename:="C:\Users\611892636\desktop\RMT Changes\RMT v8.0.2 - Huawei V1.2.xlsm")
Workbooks("RMT v8.0.2 - Huawei V1.2.xlsm").Activate
Workbooks("RMT v8.0.2 - Huawei V1.2.xlsm").Sheets("Input").Activate

Set sht = Workbooks("RMT v8.0.2 - Huawei V1.2.xlsm").Worksheets("Input")
Set sht1 = Workbooks("RMT v8.0.2 - Huawei V1.2_OI-408866.xlsb").Worksheets("MUForecasts")

Workbooks("RMT v8.0.2 - Huawei V1.2_OI-408866.xlsb").Activate
Workbooks("RMT v8.0.2 - Huawei V1.2_OI-408866.xlsb").Sheets("MUForecasts").Activate

n = sht1.Range("B" & Rows.Count).End(xlUp).Row
m = sht.Range("B" & Rows.Count).End(xlUp).Row

    For i = 13 To m
        For k = 3 To n
            Debug.Print i, k

            FGroup = sht.Range("H" & i)
            PSkills = sht1.Range("R" & k)

            RegCoun = sht.Range("L" & i)
            WrCoun = sht1.Range("P" & k)

            MRCode = sht.Range("X" & i)
            RCode = sht1.Range("Q" & k)

            If FGroup = PSkills Then
               If RegCoun = WrCoun Then
                    If MRCode = RCode Then
                        sht.Cells(i, 2).Copy
                        sht1.Cells(k, 27).Select
                        Selection.PasteSpecial Paste:=xlPasteValues
                    End If
                End If
            End If
         Next k
    Next i

'wbk.Close
Set wbk = Nothing
Set sht = Nothing
Set sht1 = Nothing
Application.DisplayAlerts = True

End Sub

"Why isn't my code working" isn't a specific enough question. I think you have the wrong idea about how this site works. Please see help center and also How to Ask as well as minimal reproducible example. You can edit your question to provide the required information, examples and a specific question, and to clean up the formatting. — ashleedawg
Hi Sir, Sorry for inconvenience you faced, I have modified the question, please relook....it would be great help. — Amit
Could you show a worked example of data in, to matching criteria logic applied, to expected data out? — QHarr
Hi QHarr, I have put a image of Demo output, in this manner I need the data by matching the 3 criteria which I have mentioned in my query. Thanks for help. — Amit

sunsetsurf sunsetsurf · Accepted Answer · 2018-04-10T14:22:59

You could try an advanced filter approach and tie it into your code.

    Sheets("Emps").Range("A1:D8").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Emps").Range("F5:F6"), CopyToRange:=Range("A1:B1"), _
    Unique:=False

Data to selectively copy:

Data copied:

Reference this short YouTube video; You can record a marco to help yourself with the code also:
https://www.youtube.com/watch?v=bGUKjXmEi2E

A more thorough tutorial is found here:
http://www.contextures.com/xladvfilter01.html

This tutorial shows how to get the source data from outside Excel:
https://www.extendoffice.com/documents/excel/4189-excel-dynamic-filter-to-new-sheet.html

This tutorial shows how to split data values based on a column to different sheets (Fruit column; Apple sheet, Pear sheet, etc.):
https://www.extendoffice.com/documents/excel/2884-excel-save-filtered-data-new-sheet-workbook.html

copy the cell value with matching multiple criteria from two sheets

1 Answers