Delete Row when cell contains another cell text

[Kell2Jam]

Hi, very new to this.

I need help with finding data from one sheet to finding data in another sheet and deleting that row.
I have had a lot of trouble with this and cant seem to crack it.
Can anyone help a formula?

 

[MARK858]

First of all welcome to the board. A few points below...

and deleting that row...
Can anyone help a formula?

You can't delete with a formula, you would need VBA.

A few bits of info that we would need to know to write any VBA...
The names of both sheets
How you are determining the data to Find.
What Ranges/Columns we are looking at.
Basically we need a more detailed description of what you are doing. Describe how you would do the task manually.

 

[Kell2Jam]

ok. so manually i would copy the row i intend to use, go over to another worksheet and paste it over the row with the same data. the purpose of this is to take data collected previously and add it to a newly updated data sheet.

I have attached pictures to give a better indication as to what needs to be done.

Example:Last report tab A2:J2 needs to be pasted over A3:J3 in Open PO's Report as they are the same Purch.Doc

 

[MARK858]

If your layout matches what you have posted then try the code below. Make sure that you test it on a copy of your workbook.

Sub Test1()
    Dim myCell As Range, endCell As Long, destCell As Range
    Application.ScreenUpdating = False
    
    With Sheets("Last report")
        For Each myCell In .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row)
            If myCell.Value <> myCell.Offset(-1).Value Then
                endCell = .Columns("A").Find(myCell.Value, , xlValues, , xlByRows, xlPrevious).Row
                Set destCell = Sheets("Open PO's").Columns("A").Find(myCell.Value, , xlValues, , xlByRows, xlNext)
                Range(.Cells(myCell.Row, "A"), .Cells(endCell, "J")).Copy destCell
            End If
        Next
    End With
    
    Application.ScreenUpdating = True
End Sub

 

[Kell2Jam]

I got a BUG.

 

[MARK858]

Might help if you state what the error says

 

[Kell2Jam]

Run-time error '9':

Subscription out of range

 

[MARK858]

That means that the sheet name is incorrect. Check the spelling is correct and that you have no leading or trailing spaces. Probably needs changing from "Open PO's" to "Open PO's Report".

 

[Kell2Jam]

Works Perfectly.

Thank You!!!

 

[MARK858]

You're welcome