Macro to filter sheet the copy data into different columns?

[tonywatsonhelp]

Hi Everyone,
I thought this would be easy but once again, No! so please help if you can

I have input column in sheet "Sold" Column "A"

in here we add a number say 100 (whatever row i paste into is the first row of the copied data)

then in my "product" sheet I have a list of all my items,

so i need to filter "Product" Column D for "100"
Then copy and paste however many rows i got into the sheet "Sold"

But I want
whats in "Product" Column "A" to go Into "Sold" Column "J"
whats in "Product" Column "C" to go Into "Sold" Column "F"
whats in "Product" Column "L" to go Into "Sold" Column "B" (All just values)

now just to make it more complicated, there is data in the rows below so first i need to instert Rows into sheet "Sold" but only Columns A:M (shift down)

please help if you can
thanks
Tony

 

[JLGWhiz]

See if this does what you want. Copy the code to Sheets "Sold" code module. The code runs when changes occur in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Columns("A") Is Nothing Then
        With Sheets("Product")
            .UsedRange.AutoFilter 4, Target.Value
            Intersect(.UsedRange.Offset(1), .Columns("A")).Copy Cells(Target.Row, "J")
            Intersect(.UsedRange.Offset(1), .Columns("C")).Copy Cells(Target.Row, "F")
            Intersect(.UsedRange.Offset(1), .Columns("L")).Copy Cells(Target.Row, "B")
            Application.DisplayAlerts = False
            Intersect(.UsedRange.Offset(1), .Columns("D")).Copy Cells(Target.Row, "A")
            Application.DisplayAlerts = True
        End With
    End If
End Sub