Copy rows from one sheet to another

Is there a formula to be able to Copy rows from one sheet to another?

If column C on Sheet 2 = Column D on Sheet 1 then copy A, B, C  from Sheet 2 to Sheet 1 starting at A for the matching row to end of used row.
Formuladd.zip
leezacAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Harry LeeCommented:
You can use Index & Match combo for the job.
Formuladd.xls
0
Harry LeeCommented:
Or you can use VBA to do the job.

Look at the Macro I have created call MatchRecord.
Formuladd.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
leezacAuthor Commented:
Great thanks.  One quick question to HarryHYLe

What if value in sheet 2 is blank, how do I not show "0" in the cell but  leave it blank?
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

leezacAuthor Commented:
Also,  for the VBA that looks like easy to use - I tried to modify it for my "real" file just testing one row.    The matches will be starting at row 3 with D column on Sheet 1 and A column on Sheet 2.   I am getting an error "Unable to match property of worksheet function".


Sub MatchRecord()
Dim I As Integer, WS As Worksheet, SrhWS As Worksheet

Set WS = Sheets("Sheet1")
Set SrhWS = Sheets("Sheet2")

For I = 1 To WS.Range("D3").End(xlDown).Row
    MatchIndex = Application.WorksheetFunction.Match(WS.Range("D" & I), Sheets("Sheet2").Range("A:A"), 0)
    If IsNull(MatchIndex) Then
        Resume Next
    Else
        WS.Range("V" & I).Value = SrhWS.Range("S" & MatchIndex).Value
       ' WS.Range("B" & I).Value = SrhWS.Range("B" & MatchIndex).Value
       ' WS.Range("C" & I).Value = SrhWS.Range("C" & MatchIndex).Value
    End If

Next

End Sub
0
leezacAuthor Commented:
Nevermind  - I changed

MatchIndex = Application.WorksheetFunction.Match(WS.Range("D" & I), Sheets("Sheet2").Range("A:A"), 0)

to

this

MatchIndex = Application.WorksheetFunction.Match(WS.Range("D3" & I), Sheets("Sheet2").Range("A:A"), 0)
0
leezacAuthor Commented:
I am going to use the VBA  and still need to know how to change the "0" to and empty cell.....

Thanks
0
leezacAuthor Commented:
Thank you
0
Harry LeeCommented:
leezac,

1) If your record start at row 3, instead of change "D" to "D3", you should change the I = 3 To WS.Range("D1").End(xlDown).Row

So the code should look like,

Sub MatchRecord()
Dim I As Integer, WS As Worksheet, SrhWS As Worksheet

Set WS = Sheets("Sheet1")
Set SrhWS = Sheets("Sheet2")

For I = 3 To WS.Range("D3").End(xlDown).Row
    MatchIndex = Application.WorksheetFunction.Match(WS.Range("D" & I), Sheets("Sheet2").Range("A:A"), 0)
    If IsNull(MatchIndex) Then
        Resume Next
    Else
        WS.Range("E" & I).Value = SrhWS.Range("B" & MatchIndex).Value
        WS.Range("F" & I).Value = SrhWS.Range("C" & MatchIndex).Value
        WS.Range("G" & I).Value = SrhWS.Range("D" & MatchIndex).Value
        WS.Range("H" & I).Value = SrhWS.Range("E" & MatchIndex).Value
        WS.Range("I" & I).Value = SrhWS.Range("F" & MatchIndex).Value
        WS.Range("J" & I).Value = SrhWS.Range("G" & MatchIndex).Value
        WS.Range("L" & I).Value = SrhWS.Range("I" & MatchIndex).Value
        WS.Range("M" & I).Value = SrhWS.Range("J" & MatchIndex).Value
        WS.Range("N" & I).Value = SrhWS.Range("K" & MatchIndex).Value
        WS.Range("O" & I).Value = SrhWS.Range("K" & MatchIndex).Value
        WS.Range("P" & I).Value = SrhWS.Range("M" & MatchIndex).Value
        WS.Range("Q" & I).Value = SrhWS.Range("N" & MatchIndex).Value
        WS.Range("R" & I).Value = SrhWS.Range("O" & MatchIndex).Value
        WS.Range("S" & I).Value = SrhWS.Range("P" & MatchIndex).Value
        WS.Range("T" & I).Value = SrhWS.Range("Q" & MatchIndex).Value
        WS.Range("U" & I).Value = SrhWS.Range("R" & MatchIndex).Value
        WS.Range("V" & I).Value = SrhWS.Range("S" & MatchIndex).Value
        WS.Range("W" & I).Value = SrhWS.Range("T" & MatchIndex).Value
        WS.Range("X" & I).Value = SrhWS.Range("U" & MatchIndex).Value
        WS.Range("Y" & I).Value = SrhWS.Range("V" & MatchIndex).Value
        WS.Range("AA" & I).Value = SrhWS.Range("X" & MatchIndex).Value
        WS.Range("AB" & I).Value = SrhWS.Range("Y" & MatchIndex).Value
        WS.Range("AC" & I).Value = SrhWS.Range("Z" & MatchIndex).Value
   
    End If
Next
End Sub

Open in new window


Since I is being counted from 1 To whatever number of rows is in Sheet1, If you change "D" & I to "D3" & I, it will be instead of doing D1 to D1000 (For Example), it will be doing D31 to D31000.

So this should fix your "Unable to match property of worksheet function" error.


To deal with your Sheet2 values being 0, you will have lots of IF statement to do.

Before each line of WS.Range("E3" & I).Value = SrhWS.Range("B" & MatchIndex).Value,
will have to add and IF.

Here is a sample

IF SrhWS.Range("B" & MatchIndex).Value = 0 Then
     WS.Range("E3" & I).Value = ""
Else
     WS.Range("E3" & I).Value = SrhWS.Range("B" & MatchIndex).Value
End If

You have 23 column values per row to copy, you will have to write 23 IF just like the above. Of couse, you will have to change the References.
0
leezacAuthor Commented:
This is what I have - still getting error....
may not understand your explanation

Sub MatchRecord()
Dim I As Integer, WS As Worksheet, SrhWS As Worksheet

Set WS = Sheets("Sheet1")
Set SrhWS = Sheets("Sheet2")

For I = 1 To WS.Range("D3").End(xlDown).Row
    MatchIndex = Application.WorksheetFunction.Match(WS.Range("D3" & I), Sheets("Sheet2").Range("A3:A5000"), 0)
    If IsNull(MatchIndex) Then
        Resume Next
    Else
        WS.Range("E3" & I).Value = SrhWS.Range("B" & MatchIndex).Value
        WS.Range("F3" & I).Value = SrhWS.Range("C" & MatchIndex).Value
        WS.Range("G3" & I).Value = SrhWS.Range("D" & MatchIndex).Value
        WS.Range("H3" & I).Value = SrhWS.Range("E" & MatchIndex).Value
        WS.Range("I3" & I).Value = SrhWS.Range("F" & MatchIndex).Value
        WS.Range("J3" & I).Value = SrhWS.Range("G" & MatchIndex).Value
        WS.Range("L3" & I).Value = SrhWS.Range("I" & MatchIndex).Value
        WS.Range("M3" & I).Value = SrhWS.Range("J" & MatchIndex).Value
        WS.Range("N3" & I).Value = SrhWS.Range("K" & MatchIndex).Value
        WS.Range("O3" & I).Value = SrhWS.Range("K" & MatchIndex).Value
        WS.Range("P3" & I).Value = SrhWS.Range("M" & MatchIndex).Value
        WS.Range("Q3" & I).Value = SrhWS.Range("N" & MatchIndex).Value
        WS.Range("R3" & I).Value = SrhWS.Range("O" & MatchIndex).Value
        WS.Range("S3" & I).Value = SrhWS.Range("P" & MatchIndex).Value
        WS.Range("T3" & I).Value = SrhWS.Range("Q" & MatchIndex).Value
        WS.Range("U3" & I).Value = SrhWS.Range("R" & MatchIndex).Value
        WS.Range("V3" & I).Value = SrhWS.Range("S" & MatchIndex).Value
        WS.Range("W3" & I).Value = SrhWS.Range("T" & MatchIndex).Value
        WS.Range("X3" & I).Value = SrhWS.Range("U" & MatchIndex).Value
        WS.Range("Y3" & I).Value = SrhWS.Range("V" & MatchIndex).Value
        WS.Range("AA3" & I).Value = SrhWS.Range("X" & MatchIndex).Value
        WS.Range("AB3" & I).Value = SrhWS.Range("Y" & MatchIndex).Value
        WS.Range("AC3" & I).Value = SrhWS.Range("Z" & MatchIndex).Value
   
   
   
   
   
   
   
    End If

Next
0
Harry LeeCommented:
leezac,

The reason why you are getting error is because you have added the 3 behind the cell column references.

The macro is supposed to loop through row 3 to the end of sheet but with the added 3, it's looping through row 31 to the end of sheet.

Copy my last posted macro and we will starting working from there.
0
Harry LeeCommented:
leezac,

Try the following code to see if it works the way you want.

Sub MatchRecord2()
Dim I As Integer, WS As Worksheet, SrhWS As Worksheet

Set WS = Sheets("Sheet1")
Set SrhWS = Sheets("Sheet2")

For I = 1 To WS.Range("D3").End(xlDown).Row
    MatchIndex = Application.WorksheetFunction.Match(WS.Range("D3" & I), Sheets("Sheet2").Range("A:A"), 0)
    If IsNull(MatchIndex) Then
        Resume Next
    Else
        WS.Range("E3" & I).Value = SrhWS.Range("B" & MatchIndex).Value
        WS.Range("F3" & I).Value = SrhWS.Range("C" & MatchIndex).Value
        WS.Range("G3" & I).Value = SrhWS.Range("D" & MatchIndex).Value
        WS.Range("H3" & I).Value = SrhWS.Range("E" & MatchIndex).Value
        WS.Range("I3" & I).Value = SrhWS.Range("F" & MatchIndex).Value
        WS.Range("J3" & I).Value = SrhWS.Range("G" & MatchIndex).Value
        WS.Range("L3" & I).Value = SrhWS.Range("I" & MatchIndex).Value
        WS.Range("M3" & I).Value = SrhWS.Range("J" & MatchIndex).Value
        WS.Range("N3" & I).Value = SrhWS.Range("K" & MatchIndex).Value
        WS.Range("O3" & I).Value = SrhWS.Range("K" & MatchIndex).Value
        WS.Range("P3" & I).Value = SrhWS.Range("M" & MatchIndex).Value
        WS.Range("Q3" & I).Value = SrhWS.Range("N" & MatchIndex).Value
        WS.Range("R3" & I).Value = SrhWS.Range("O" & MatchIndex).Value
        WS.Range("S3" & I).Value = SrhWS.Range("P" & MatchIndex).Value
        WS.Range("T3" & I).Value = SrhWS.Range("Q" & MatchIndex).Value
        WS.Range("U3" & I).Value = SrhWS.Range("R" & MatchIndex).Value
        WS.Range("V3" & I).Value = SrhWS.Range("S" & MatchIndex).Value
        WS.Range("W3" & I).Value = SrhWS.Range("T" & MatchIndex).Value
        WS.Range("X3" & I).Value = SrhWS.Range("U" & MatchIndex).Value
        WS.Range("Y3" & I).Value = SrhWS.Range("V" & MatchIndex).Value
        WS.Range("AA3" & I).Value = SrhWS.Range("X" & MatchIndex).Value
        WS.Range("AB3" & I).Value = SrhWS.Range("Y" & MatchIndex).Value
        WS.Range("AC3" & I).Value = SrhWS.Range("Z" & MatchIndex).Value

    End If

Next
End Sub
Sub MatchRecord2_1()
Dim I As Integer, WS As Worksheet, SrhWS As Worksheet

Set WS = Sheets("Sheet1")
Set SrhWS = Sheets("Sheet2")

For I = 3 To WS.Range("D3").End(xlDown).Row
    MatchIndex = Application.WorksheetFunction.Match(WS.Range("D" & I), Sheets("Sheet2").Range("A:A"), 0)
    If IsNull(MatchIndex) Then
        Resume Next
    Else

        If SrhWS.Range("B" & MatchIndex).Value = 0 Then
            WS.Range("E" & I).Value = ""
        Else
            WS.Range("E" & I).Value = SrhWS.Range("B" & MatchIndex).Value
        End If

        If SrhWS.Range("C" & MatchIndex).Value = 0 Then
            WS.Range("F" & I).Value = ""
        Else
            WS.Range("F" & I).Value = SrhWS.Range("C" & MatchIndex).Value
        End If

        If SrhWS.Range("D" & MatchIndex).Value = 0 Then
            WS.Range("G" & I).Value = ""
        Else
            WS.Range("G" & I).Value = SrhWS.Range("D" & MatchIndex).Value
        End If

        If SrhWS.Range("E" & MatchIndex).Value = 0 Then
            WS.Range("H" & I).Value = ""
        Else
            WS.Range("H" & I).Value = SrhWS.Range("E" & MatchIndex).Value
        End If

        If SrhWS.Range("F" & MatchIndex).Value = 0 Then
            WS.Range("I" & I).Value = ""
        Else
            WS.Range("I" & I).Value = SrhWS.Range("F" & MatchIndex).Value
        End If

        If SrhWS.Range("G" & MatchIndex).Value = 0 Then
            WS.Range("J" & I).Value = ""
        Else
            WS.Range("J" & I).Value = SrhWS.Range("G" & MatchIndex).Value
        End If

        If SrhWS.Range("I" & MatchIndex).Value = 0 Then
            WS.Range("L" & I).Value = ""
        Else
            WS.Range("L" & I).Value = SrhWS.Range("I" & MatchIndex).Value
        End If

        If SrhWS.Range("J" & MatchIndex).Value = 0 Then
            WS.Range("M" & I).Value = ""
        Else
            WS.Range("M" & I).Value = SrhWS.Range("J" & MatchIndex).Value
        End If

        If SrhWS.Range("K" & MatchIndex).Value = 0 Then
            WS.Range("N" & I).Value = ""
        Else
            WS.Range("N" & I).Value = SrhWS.Range("K" & MatchIndex).Value
        End If

        If SrhWS.Range("K" & MatchIndex).Value = 0 Then
            WS.Range("O" & I).Value = ""
        Else
            WS.Range("O" & I).Value = SrhWS.Range("K" & MatchIndex).Value
        End If

        If SrhWS.Range("M" & MatchIndex).Value = 0 Then
            WS.Range("P" & I).Value = ""
        Else
            WS.Range("P" & I).Value = SrhWS.Range("M" & MatchIndex).Value
        End If

        If SrhWS.Range("N" & MatchIndex).Value = 0 Then
            WS.Range("Q" & I).Value = ""
        Else
            WS.Range("Q" & I).Value = SrhWS.Range("N" & MatchIndex).Value
        End If

        If SrhWS.Range("O" & MatchIndex).Value = 0 Then
            WS.Range("R" & I).Value = ""
        Else
            WS.Range("R" & I).Value = SrhWS.Range("O" & MatchIndex).Value
        End If

        If SrhWS.Range("P" & MatchIndex).Value = 0 Then
            WS.Range("S" & I).Value = ""
        Else
            WS.Range("S" & I).Value = SrhWS.Range("P" & MatchIndex).Value
        End If

        If SrhWS.Range("Q" & MatchIndex).Value = 0 Then
            WS.Range("T" & I).Value = ""
        Else
            WS.Range("T" & I).Value = SrhWS.Range("Q" & MatchIndex).Value
        End If

        If SrhWS.Range("R" & MatchIndex).Value = 0 Then
            WS.Range("U" & I).Value = ""
        Else
            WS.Range("U" & I).Value = SrhWS.Range("R" & MatchIndex).Value
        End If

        If SrhWS.Range("S" & MatchIndex).Value = 0 Then
            WS.Range("V" & I).Value = ""
        Else
            WS.Range("V" & I).Value = SrhWS.Range("S" & MatchIndex).Value
        End If

        If SrhWS.Range("T" & MatchIndex).Value = 0 Then
            WS.Range("W" & I).Value = ""
        Else
            WS.Range("W" & I).Value = SrhWS.Range("T" & MatchIndex).Value
        End If

        If SrhWS.Range("U" & MatchIndex).Value = 0 Then
            WS.Range("X" & I).Value = ""
        Else
            WS.Range("X" & I).Value = SrhWS.Range("U" & MatchIndex).Value
        End If

        If SrhWS.Range("V" & MatchIndex).Value = 0 Then
            WS.Range("Y" & I).Value = ""
        Else
            WS.Range("Y" & I).Value = SrhWS.Range("V" & MatchIndex).Value
        End If

        If SrhWS.Range("X" & MatchIndex).Value = 0 Then
            WS.Range("AA" & I).Value = ""
        Else
            WS.Range("AA" & I).Value = SrhWS.Range("X" & MatchIndex).Value
        End If

        If SrhWS.Range("Y" & MatchIndex).Value = 0 Then
            WS.Range("AB" & I).Value = ""
        Else
            WS.Range("AB" & I).Value = SrhWS.Range("Y" & MatchIndex).Value
        End If

        If SrhWS.Range("Z" & MatchIndex).Value = 0 Then
            WS.Range("AC" & I).Value = ""
        Else
            WS.Range("AC" & I).Value = SrhWS.Range("Z" & MatchIndex).Value
        End If
    End If
Next
End Sub

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.