Check value, insert row question - Tricky one

[johnnyvv]

The question is how would i go about:

Checking the value in a cell on sheet1. If that value is not found
in the same cell on sheet2

then

Copy entirerow on sheet1 and insert into sheet 2.
then loop through to the next cell down.

its a tough one. taking me a while.
any help will be appreciated.

 

[johnnyvv]

bump..

 

[mvptomlinson]

Are you looking to actually insert the row from Sheet1 into Sheet2 (thereby pushing all other rows on Sheet2 down one row), or are you simply trying to replace the same row on Sheet2 with the values in that row on Sheet1 if they don't match?

I ask, because if Sheet1.A5 doesn't match Sheet2.A5, and you insert a row onto Sheet2 at A5, then Sheet1.A6 may no longer match up to Sheet2.A6, etc.. Is that the tricky part you're eluding to?

 

[johnnyvv]

Yes i would like to insert a row into sheet 2. Thereby pushing all other rows down.

Yes this is very tricky.(i think anyway and im freaking out beacause i cant do it!)

 

[johnnyvv]

Actuall i just thought of something. what if you compare the 2 worksheets with one column and you set a unique character because column D of the worksheet is unique.

So it would look for the unique character in sheet1 and it is missing in sheet 2 then Copy row from sheet1 and insert to sheet2.

Loop

now that aint tricky, but i dont know how to write this code....

 

[mvptomlinson]

You actually lost me with your last post. But this code does what your first request was after.

It starts by comparing Sheet1.B2 to Sheet2.B2. If they don't match, it will copy Sheet1.Row2 and insert into Sheet2.Row2 (pushing all rows down by one). It will then compare Sheet1.B3 to Sheet2.B4 (not B3 since that was the previous comparison that got pushed down). It will continue down Sheet1.ColumnB until it reaches the bottom.

Sub moveData()
    Dim i As Long, j As Long
    j = 2
    With Sheets("Sheet1")
        For i = 2 To .Range("B65536").End(xlUp).Row
        If .Cells(i, 2).Value <> Sheets("Sheet2").Cells(j, 2).Value Then
            .Cells(i, 1).EntireRow.Copy
            Sheets("Sheet2").Cells(j, 1).Insert
            Application.CutCopyMode = False
            j = j + 2
        Else
            j = j + 1
        End If
        Next i
    End With
End Sub

 

[johnnyvv]

mvptomlinson that code is fantastic. it works but with one problem.

It copies the whole of sheet1 into sheet2.

I want it to check if the value in Column D in sheet1 is <> to the value of Column D in sheet2.

Then i want it to insert the row.

in other words if the value is different to the corresponding row number,
insert the row from sheet1 into sheet2.


any more help will be greatly appreciated.

 

[mvptomlinson]

That's probably because my code is checking against column B, not D. Adjustments would need to be made to scan through column D. Perhaps:

Sub moveData()
    Dim i As Long, j As Long
    j = 2
    With Sheets("Sheet1")
        For i = 2 To .Range("D65536").End(xlUp).Row
        If .Cells(i, 4).Value <> Sheets("Sheet2").Cells(j, 4).Value Then
            .Cells(i, 1).EntireRow.Copy
            Sheets("Sheet2").Cells(j, 1).Insert
            Application.CutCopyMode = False
            j = j + 2
        Else
            j = j + 1
        End If
        Next i
    End With
End Sub

This starts in row 2. If you need to start in a different row, change the values in green to that row number.

 

[johnnyvv]

YOU ARE MY ABSOLUTE HERO!
seriously you should be proud you just helped me out a alot.



the only thing was it added double lines so
i changed it from
j = j + 2
Else
j = j + 1

to

j = j + 1
Else
j = j + 1

and it was PERFECt.

thankyou.

 

[mvptomlinson]

Glad I could help you out.