Macro to filter sheet the copy data into different columns?

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,721
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
See if this does what you want. Copy the code to Sheets "Sold" code module. The code runs when changes occur in column A.

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,530
Messages
5,625,362
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top