Link to home
Start Free TrialLog in
Avatar of leezac
leezac

asked on

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
Avatar of Harry Lee
Harry Lee
Flag of Canada image
You can use Index & Match combo for the job.
Formuladd.xls
ASKER CERTIFIED SOLUTION
Avatar of Harry Lee
Harry Lee
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of leezac
leezac

ASKER

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?
Avatar of leezac
leezac

ASKER

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
Avatar of leezac
leezac

ASKER

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)
Avatar of leezac
leezac

ASKER

I am going to use the VBA  and still need to know how to change the "0" to and empty cell.....

Thanks
Avatar of leezac
leezac

ASKER

Thank you
Avatar of Harry Lee
Harry Lee
Flag of Canada image
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.
Avatar of leezac
leezac

ASKER

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
Avatar of Harry Lee
Harry Lee
Flag of Canada image
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.
Avatar of Harry Lee
Harry Lee
Flag of Canada image
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