See attached file, I hope it's what you need.
I added this macro on sheets Day and Night:
Private Sub Worksheet_Change(ByVal target As Range)
If target.Cells.Count = 1 _
And target.Column = 6 Then
Call Module1.MoveData(ActiveSheet, target)
End If
End Sub
and this macro on Module1
Sub MoveData(ws As Worksheet, target As Range)
Dim sourceWs As Worksheet
Dim found As Range
Dim myCols As String, myCol As Variant
Dim i As Integer, sourceCol As String
myCols = "d,h,f,g,i"
Set sourceWs = ThisWorkbook.Sheets("order history")
Set found = sourceWs.Range("m:m").Find(target, LookIn:=xlValues)
For i = 0 To UBound(Split(myCols, ","))
sourceCol = Split(myCols, ",")(i)
If Not found Is Nothing Then
ws.Cells(target.Row, i + 1) = sourceWs.Cells(found.Row, sourceCol)
Else
ws.Cells(target.Row, i + 1) = ""
End If
Next
End Sub
Regards,
Antonio
Bookmarks