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.
doIt Worksheets("Sheet1"), Worksheets("Sheet2")
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
rowNum1 = .Rows.Count
colNum1 = .Columns.Count
rowNum2 = .Rows.Count
colNum2 = .Columns.Count
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
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.