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.