Copying data to section of another worksheet

[alien3011]

Hi,I have attached the example file.

I am suppose to filter Sheet2 Column "AD" to 'Y' and copy the data to Sheet 1 to their section by ItemNumber.
Highlight those that are copied over to Sheet 1 with a certain cell colour, after copied, it will set the status of row to 'N'.

I have tried to do a macro for it.
There are some problems to my codes:

It copies the filtered data(which are 'N' ,hidden) and only run for the first itemNumber.

Anyone can help me solve this problem? Thanks a lot

[alien3011]

Anyone able to help?


Lastest code i am using

Problems encountered:

It only does not add other itemNumber except the first one. I need to add all of the data the last row in their section by itemNumber.

Sub AddSOtoSA()
Dim SO As Worksheet
Dim SA3 As Worksheet
Dim SAlastrow As Long


Set SO = Sheets("Sheet2")
Set SA3 = Sheets("Sheet1")
SAlastrow = ThisWorkbook.Sheets("Sheet1").Range("G" & Rows.count).End(xlUp).row

Application.ScreenUpdating = False

With SO
'===============================================================
'This is to set the autofilter to filter out those that at 'N'
'===============================================================
'*****************
'if range error occur, go to worksheet sales order and select any data cells
'**************
.Range("1:1").Select
Selection.AutoFilter Field:=29, Criteria1:="Tij 3"
Selection.AutoFilter Field:=30, Criteria1:="Y"

'===============================================================
'This foreach loop reads through Sales Order worksheet data that are filtered
'===============================================================
For Each ce In SO.Range("G2:G" & SO.Cells(Rows.count, "G").End(xlUp).row).SpecialCells(xlCellTypeVisible)

'------------------------------------------
'looping through Shipment allocation
'to see if part number matches
'with SO number
'------------------------------------------
For i = 2 To SAlastrow Step 1

'-----------------------------------------
'Check if current cell in SA is equals to SO number
'---------------------------------------
If .Cells(i, "g") = ce.Value Then
'-------------------------------------------------------
'Check if next cell of SA is not equals to SO
'then insert an empty row
'copy the entire row data from SO and insert to empty row
'-------------------------------------------------------
If .Cells(i + 1, "g") <> ce.Value Then
Worksheets("Sheet1").Rows(i + 1 & ":" & i + 1).Insert
ce.EntireRow.Copy Destination:=Worksheets("Sheet1").Rows(i + 1 & ":" & i + 1)

Exit For
End If
Else

End If



Next i
Next ce
Selection.AutoFilter Field:=29
Selection.AutoFilter Field:=30
End With

Range("A2").Select
Application.ScreenUpdating = True


End Sub