Sometime back, we provided an Excel Macro to compare two excel worksheets and highlight duplicate entries. One of our readers left a comment on this post for us to provide a Macro to copy values from one worksheet to another worksheet based on values matching in one of the columns.

We are re-writing the comment here for our readers to understand the exact requirement. Here it goes:

I have a column with 80 names and in the next 2 columns I have description corresponding to that name.

But now i have another data with same 80 names but the order is shuffled. I want a macro for copy paste the the description corresponding to the name.

eg: column 1 : a ,b ,c ,d ,e
column 2 : This is a , This is b, This is c, This is d, This is e

Now my column changed to b ,c , a , e , d

Macro should be for arranging like: This is b , This is c, This is a, This is e, This is d

Now, the good news is that we have a solution to share with you. Below is code for Macro to copy from one worksheet to another worksheet conditionally.

To find out the detailed steps on how to create, run or edit a macro, you may refer here.

Sub Copy_Paste()
 
doIt Worksheets("Sheet1"), Worksheets("Sheet2")
End Sub
 
Sub doIt(ws1 As Worksheet, ws2 As Worksheet)
Dim data2 As String, data1 As String
Dim rowNum1 As Long, rowNum2 As Long
Dim colNum1 As Long, colNum2 As Long
 
Dim column1 As Integer, column2 As Integer
 
With ws1.UsedRange
rowNum1 = .Rows.Count
colNum1 = .Columns.Count
End With
With ws2.UsedRange
rowNum2 = .Rows.Count
colNum2 = .Columns.Count
End With
 
column1 = 1
column2 = 2
For r2 = 1 To rowNum2
    data2 = ws2.Cells(r2, column1).FormulaLocal
    LFound = False
    For r1 = 1 To rowNum1
        data1 = ws1.Cells(r1, column1).FormulaLocal
        If data1 = data2 Then
            For c1 = 2 To colNum1
                ws1.Select
                ws1.Cells(r1, c1).Select
                Selection.Copy
                ws2.Select
                ws2.Cells(r2, c1).Select
                ActiveSheet.Paste
            Next c1
            Exit For
        End If
    Next r1
Next r2
 
End Sub

Few Points Worth To Note:

• While creating the Macro, ensure that name of the macro is kept as Copy_Paste.
• Worksheet names are considered to be “Sheet1″ and “Sheet2″
• This Macro compare each cell in first column of Sheet 2 with each cell in first column of Sheet 1. Data in all columns of row matched in Sheet1 is copied to corresponding row in Sheet2.

Hope you find this solution useful, your suggestions, feedback and comments are always welcome.