Move data from a worksheet to another one with cell value on those 2 worksheet

[michellin]

Hi there,

I have look everywhere to found something to help me.

I have two worksheet of data. worksheet 1 with 1 sheet, and worksheet 2 with 1 sheet.
my column A is 6 digit code, for over 5000 ligne.

I need to find a macro, to run on worksheet 2, to look into worksheet1 (sheet1) in column A, too find the 6 digit number and cut the line, and paste it into worksheet 2(sheet1) at the same 6 digit number.

Like this
worsksheet 2(sheet1) run macroX, find into column A 660651, go to worsheet 1(sheet1) find 660651 cut the line and paste it over the line in worksheet 2 (sheet1) column A 660651., and repeat for each numbers in column A.

Usefull information, they dont have all the same 6 digit. in column A worksheet 2 is the main base with over 5000 line. worksheet 1 have around 1000 line. It why it hard for me, if it was exactly the same, i will just cut and paste.
I need to search manually in it each row, it's time comsuming.

I know a macro would be able to do it, but i'm still too noob to program it. I can modify it, but not do it all along.

I ask sometimes here help when i'm stuck. I know here a lot of pro's, can help me. So a big thanks in advance for those helping me.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot of each sheet (not a picture). Better still, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[michellin]

Dropbox

www.dropbox.com

I hope you can see it.

i will work on worksheet2(feuil1), everything need to be bring to worksheet 2 like a main base.

the macro need to run on worksheet2, to look on worksheet1(feuil1) into column A, the 6 digit number (the first one) 680002, search it in worksheet2(feuil1) column A, find the same digit 680002.

When find the same number, cut the line on worksheet1, too paste special (with form and color) too worksheet2 on the same 6 digit number 680002.

then find the second number 680007, till the end of the worksheet1.

I make those 2 ''empty sheet'' because i have a lot to do on a lot of worksheet.
The macro will be enormus if we have to enter all those worksheet.
So i will copy paste my data always on main worksheet2 and the stuff to find on worksheet 1, then export them where i want them after it.
It will be more easy for everybody.

Thanks a lot, if you need something else just tell me.

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot of each sheet (not a picture). Better still, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[mumps]

Make sure that both workbooks are open. Place this macro in worksheet2.xlsx

Sub MatchData()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWS As Worksheet, arr As Variant, desRng As Range, x As Long
    Set srcWS = Workbooks("worksheet1.xlsx").Sheets(1)
    Set desWS = Sheets(1)
    arr = srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp)).Value
    Set desRng = desWS.Range("A2", desWS.Range("A" & desWS.Rows.Count).End(xlUp))
    For i = 1 To UBound(arr, 1)
        If Not IsError(Application.Match(arr(i, 1), desRng, 0)) Then
            x = Application.Match(arr(i, 1), desRng, 0)
            srcWS.Rows(i + 1).EntireRow.Copy desWS.Range("A" & x + 1)
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[michellin]

Make sure that both workbooks are open. Place this macro in worksheet2.xlsx

Sub MatchData()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWS As Worksheet, arr As Variant, desRng As Range, x As Long
    Set srcWS = Workbooks("worksheet1.xlsx").Sheets(1)
    Set desWS = Sheets(1)
    arr = srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp)).Value
    Set desRng = desWS.Range("A2", desWS.Range("A" & desWS.Rows.Count).End(xlUp))
    For i = 1 To UBound(arr, 1)
        If Not IsError(Application.Match(arr(i, 1), desRng, 0)) Then
            x = Application.Match(arr(i, 1), desRng, 0)
            srcWS.Rows(i + 1).EntireRow.Copy desWS.Range("A" & x + 1)
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

OMG mumps, you are the king from the king, or queen of queen...

Its work really good. Those loop kill me, i can program some wasy macro with the record and modifi it. But everytime a need a loop, i'm really lost.
But you justa save me from a big headache to joint all those line together one by one.

Thanks a lot really

 

[mumps]

You are very welcome.