Matching the Cells then copy paste the Data VBA

ArhamRajput · Aug 10th 2021

I need to create a VBA code that is I have two Sheets in a file. Sheet1 and Sheet2.

I want to match Sheet2 values these values in Sheet1 Column A and Column F if data matches then copy the corresponding columns Data that are Column B,C,D,E and Paste into Sheet2 Column E,F,G,H.

I also wants to match Sheet2 C3 value that is Column3 in Sheet1 in the Range("G1:Z1") if Column Header is matches then copy the Matched Column Data into Sheet2 Column "i".

the thing i want is to match the Sheet2 Range A3, B3 and C3 into Sheet1 and copy paste the Data. I have attached the file with example your help will be much appreciated.

Mumps · Aug 10th 2021

Will columns A, B and C in Sheet2 always have only one row with data (row 3) as in the file you attached or can you have more than one row with data, for example in rows 3, 4, 5 and 6?

ArhamRajput · Aug 10th 2021

Will columns A, B and C in Sheet2 always have only one row with data (row 3) YES

Because these are criteria where i have added validation for columns A3, B3 and C3. That will change and data will be copy and pasted accordinlgy

Mumps · Aug 11th 2021

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Sheet2 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in A3, then make a selection in B3 and lastly, make a selection in C3. It is very important that the selection in C3 is made last because that selection triggers the macro.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub
    Application.ScreenUpdating = False
    Dim v As Variant, srcWS As Worksheet, lCol As Long, dic As Object, i As Long, val1 As String, val2 As String, fnd As Range
    Set srcWS = Sheets("Sheet1")
    With srcWS
        Set fnd = .Rows(1).Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
        val2 = Target.Offset(, -2) & "|" & Target.Offset(, -1) & "|" & Target
        lCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        v = .Range("A2", .Range("A" & .Rows.Count).End(xlUp)).Resize(, lCol).Value
        v2 = Range("A3:C3").Value
        Set dic = CreateObject("Scripting.Dictionary")
        For i = LBound(v) To UBound(v)
            val1 = v(i, 6) & "|" & v(i, 1) & "|" & Target
            If Not dic.exists(val1) Then
                dic.Add val1, i
            End If
        Next i
        If dic.exists(val2) Then
            Cells(Rows.Count, "E").End(xlUp).Offset(1).Resize(, 4).Value = Array(v(dic(val2), 2), v(dic(val2), 3), v(dic(val2), 4), v(dic(val2), 5))
            Cells(Rows.Count, "I").End(xlUp).Offset(1).Value = v(dic(val2), fnd.Column)
        End If
    End With
    Application.ScreenUpdating = True
End Sub

Display More

ArhamRajput · Aug 11th 2021

Mumps Thanks this is literally a marvelous attempt that you make. I exactly want this.

Mumps · Aug 12th 2021

You are very welcome.

Matching the Cells then copy paste the Data VBA

Participate now!

Share

Tags