Copy Row of Data If Two Cells Match

[CH1990]

Good Morning

I am after a formula/macro that will copy a row of data if two certain cells match.

In sheet 25 (Called 'All Orders') I have headings in the columns A-O. The headings are in row 2 and the data starts in row 3.

Sheet 26 (Called 'Wolds') is set out in the exact same lay out.

What I need to do is; If Column E in 'All Orders' matches the text in cell A1 in 'Wolds' then copy the row of information from 'All Orders' to 'Wolds'

In Column 'P' in 'Wolds' I have a code to number each order (W1, W2, W3...ETC.) I need this row to remain.

In previous attempts at doing this macro every time it ran it would re add all the orders so some were being duplicated several times on the 'wolds' sheet. I am looking for new orders to be added to the previous one when the macro is run

Thank you.

<tbody>
</tbody>

 

[WatfordKev]

I hope it's OK just to add that I'm also looking for something very similar to this - I am sure VLOOKUP would work but I've been unable to refine it!!!

 

[CH1990]

Yes, I have also tried to use VLOOKUP but can't seem to get it exactly spot on

 

[CH1990]

Any body know the above is possible? Looking at putting the spread sheet to use on Monday so may need to go a long way round if a macro is not available. Thanks

 

[Smeggy]

OK this is something similar but works on a sinlge page perhaps you could modify it to work across 2 pages.
This looks for "Aword" in the columns A to C, if it finds it, it then loos in column D for "Bword", if that matches it then enters "Anther Word" in Column E..
should be possible to modify the code into what you need.

Sheets("Sheet1").Select
With Range(Columns(1), Columns(3))
Set c = .Find("Aword", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Activate
With ActiveCell.Offset(0, 1).Select
If ActiveCell.Value = "Bword" Then
ActiveCell.Offset(0, 1).Value = "Another Word"
End If
End With
Set c = .FindNext(c)
Loop Until c.Address = firstAddress
End If
End With

 

[Smeggy]

Hi Again,
This probably isn't as pretty as some of the guys on here but it might give you a better starting point.


Sub Wold()

Dim Order As String
Dim FoundCell As Range
Dim LastCell As Range
Dim FirstAddr As String


xrow = 3

Do While Not IsEmpty(ActiveCell)
Sheets("All Orders").Select
Range("E" & xrow).Select
Order = ActiveCell.Value

Sheets("Wolds").Select

With Range("A1:A10")
Set LastCell = .Cells(.Cells.Count)
End With
Set FoundCell = Range("A1:A10").Find(what:=Order, after:=LastCell)
If Not FoundCell Is Nothing Then
FirstAddr = FoundCell.Address
End If

Debug.Print FoundCell.Address
Set FoundCell = Range("A1:A10").FindNext(after:=FoundCell)
If FoundCell.Address = FirstAddr Then

Sheets("All Orders").Select
ActiveCell.EntireRow.Copy
Sheets("Wolds").Select
With ActiveSheet
Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
LastCellRowNumber = LastCell.Row
LastCell.Activate
ActiveCell.Offset(1, 0).Activate
End With
ActiveCell.PasteSpecial xlPasteValues
End If
xrow = xrow + 1
Sheets("All Orders").Select
Range("E" & xrow).Select
Order = ActiveCell.Value

Loop
End Sub

 

[CH1990]

Thank you but this doesn't seem to work. I copy it into the code, been through it and made a couple of adjustments, but when I go to run it, nothing happens