Conditional Copy Paste From One WorkSheet To Another WorkSheet In Excel

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.Cells(r1, c1).Select
                ws2.Cells(r2, c1).Select
            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.

  1. I need a macro that compares one column (C)on sheet1 to column (A) on sheet2 and the copies the entire row from sheet1 to sheet3 and then removes that row from sheet one.

  2. I need a macro to compare two columns(fname,lname) in sheet2 with the same two columns(fname,lname) in sheet1.if both the column is available in sheet1.Then have to move the corresponding 3rd column value in sheet1 to sheet2

  3. Hi , please look on to the below logic and suggest an example code for the logic

    when i give input in a cell in sheet 1 and click on Enter button the value should be taken to sheet2 and copy the value in a cell (A1) in sheet 2

    again when i give a value in the same cell in sheet 1 and clik on enter the value should be copied in the cell(a2) in sheet2


    Entering “1″ in A1 cell in Sheet 1
    Cliking on enter
    “1″ should be copied in A1 cell in sheet 2

    Entering “2″ in A1 cell in Sheet1
    Cliking on enter
    “2″ should be copied in A2 cell in sheet2

    So on…… when ever i enter a value in A1 the value should be cut copy pasted in the sheet2 cells one by one

  4. Hi Guys i have a problem. i dont have a proper knowledge of macro writing however i need a macro to copy date and values in a column located in a master workbook spreadsheet. and paste in a different workbook sheet 1 under a set heading. also convert values from feet to meters and delete rows that have no values or date. The macro must open the workbook copy the column of data and open the workbook and paste under the heading in the template. and save as name, also xml.

    Note: there are several columns of date and values located to the right in the document.
    Hope you can help!

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>