Copy and Paste After Find

[Mike H]

Afternoon guys,

I have one workbook with 2 sheets of data, what i need to do is build a macro that look at column A in sheet 1, matches that to column A in sheet 2.

For every match in the columns (And there will be no duplicates) copy the data row F to M in sheet 2 and paste it on the matching row from F onwards in sheet 1.

I have no idea where to start with this as i'm building my knowledge of VBA as you guys help me.

So thank alot in advance...

Mike

 

[Lewiy]

As you have asked for a macro, that is what I have provided, however, this could also be done with a VLOOKUP if there are no duplicates:

Sub SortValues()
Dim Limit As Long
Dim Limit2 As Long
Dim c As Long
Dim d As Long
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
Limit = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Limit2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
For c = 1 To Limit
    For d = 1 To Limit2
        If sh1.Cells(c, 1) = sh2.Cells(d, 1) Then
            sh1.Range("F" & c & ":M" & c).Value = sh2.Range("F" & d & ":M" & d).Value
        End If
    Next d
Next c
End Sub

 

[Peter_SSs]

Maybe this?

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Copy_Paste()
    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> sh1 <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> sh2 <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
    
    <SPAN style="color:#00007F">Set</SPAN> sh1 = Sheets("Sheet1")
    <SPAN style="color:#00007F">Set</SPAN> sh2 = Sheets("Sheet2")
    lr = sh1.Range("A" & Rows.Count).End(xlUp).Row
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> sh1.Range("A1:A" & lr)
        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
        c.Offset(0, 5).Resize(, 7).Value _
            = sh2.Columns("A").Find(What:=c).Offset(0, 5).Resize(, 7).Value
        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
    <SPAN style="color:#00007F">Next</SPAN> c
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Mike H]

This works perfectly and is very simple... Thanks alot..

There is going to be thousands of rows its going to need to look at so i wanted to cut down on bulk on the excel size!!

Thanks again!!