Macro to find and copy data from a list into another sheet

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,717
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi everyone,
I have two sheets
1 called "Database"
2 called "Inputs"

Now Database has all my product names and data in it
Product Lookup is in Range D10:D500
Product E
Price F
Size G


So Heres what i want to do
In Sheets Inputs Range G7:G37 In have drop down boxes
If i choose a dropdown box the macro is triggered,

The dropdown box has Product Lookup names

so the macro should, look at cell and see name of Product look up,
find that name in Sheet "Database" range ("D10:D500") and return the Product in column E to Inputs column J, Price to Column M and Size to column P

I know this can be done with formulas but i need it to be done with macros
data must on copy over Values

please help if you can

Thanks

Tony
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,088
Office Version
  1. 2013
Platform
  1. Windows
Hi
Try this in Sheet("input") code

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim x As Range
    If Not Intersect(Target, Range("G7:C37")) Is Nothing Then
        With Sheets("Database ").Range("D10:D500")
            Set x = .Find(Target.Value2)
            Range(Target.Address).Offset(, 3) = x.Offset(, 1)
            Range(Target.Address).Offset(, 6) = x.Offset(, 2)
            Range(Target.Address).Offset(, 9) = x.Offset(, 3)
        End With
    End If
End Sub
 
Solution

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,088
Office Version
  1. 2013
Platform
  1. Windows
you are welcome
And thank you for the feedback
Be happy
 

Watch MrExcel Video

Forum statistics

Threads
1,126,997
Messages
5,622,081
Members
415,875
Latest member
Tarali

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