hi experts
first of all this file not mine when I searched in the internet I've found it and the code I got from another forum , I 've found this is soon from my post so I have no experience about vba I can mod some codes sometimes it works and often I failed , indeed I ameded this code but doesn't work well I put the expected result in sheet "invoice" based on cell value e5 or e8 if I write in e5 then pull data in e8 and g5 and the rests of data from row 16 the same thing when write in e8 bit should happen with the rests of cells , if any body help mod the code or write another macro I truly appreciate that
Sub CopyData()
Dim Cl As Range
Dim SrcSht As Worksheet
Dim DestSht As Worksheet
Dim Rng As Range
Application.ScreenUpdating = False
Set SrcSht = Sheets("data")
Set DestSht = Sheets("invoice")
With CreateObject("scripting.dictionary")
For Each Cl In DestSht.Range("e5", DestSht.Range("e" & Rows.Count).End(xlUp))
If Not .exists(Cl.Value) Then .Add Cl.Value, Nothing
Next Cl
For Each Cl In SrcSht.Range("b2", SrcSht.Range("b" & Rows.Count).End(xlUp))
If Not .exists(Cl.Value) or Cl.Offset(, 1).Value <> "" Then
' Not .exists(Cl.Value) Then
If Rng Is Nothing Then
Set Rng = Cl
Else
Set Rng = Union(Rng, Cl)
End If
End If
Next Cl
End With
Rng.EntireRow.Copy DestSht.Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub