Pasting Data Based On Drop Down

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
Hi all,

So I have an Excel Worksheet with a data validation drop down. I am trying to have data pasted based on said drop down. The data validation drop down is constantly changing.

Based on the table provided, I need Sheet 2 to populate based on a data validation drop down provided in cell A1 and A2. The thing is, the Data Validation drop down on sheet 2 is changing numbers. So one time it might be 1 and the next time it is used, it might be 500. The numbers in this case are 1 and 45.

Sheet 1
ABCD
1NumberAnimalFoodDrink
21CatDryMilk
345DogWetWater

<tbody>
</tbody>







Sheet 2
ABCD
1"Drop Down Here" Auto PopAuto PopAuto Pop
2"Drop Down Here"Auto PopAuto PopAuto Pop

<tbody>
</tbody>

So If 45 Is chosen on Sheet 2, A1, then The B-D columns from sheet 1 will be pasted into sheet 2's columns. Same goes for 1.

All help is greatly appreciated. I can try to explain more, if it is needed. Thanks in advance.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
Try this:
Copy the code then right click sheet2 tab > select View Code > paste the code.

Code:
[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)

[COLOR=Royalblue]If[/COLOR] Target.Cells.CountLarge <> [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Range([COLOR=brown]"A:A"[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        [COLOR=Royalblue]With[/COLOR] Sheets([COLOR=brown]"Sheet1"[/COLOR])
        
        [COLOR=Royalblue]Dim[/COLOR] res
        res = Application.Match(Target.Value, .Range([COLOR=brown]"A1"[/COLOR], .Cells(Rows.Count, [COLOR=brown]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp)), [COLOR=Royalblue]False[/COLOR])
            [COLOR=Royalblue]If[/COLOR] IsNumeric(res) [COLOR=Royalblue]Then[/COLOR]
            Application.EnableEvents = [COLOR=Royalblue]False[/COLOR]
            Target.Offset(, [COLOR=crimson]1[/COLOR]).Resize(, [COLOR=crimson]3[/COLOR]).Value = .Range([COLOR=brown]"B"[/COLOR] & res & [COLOR=brown]":D"[/COLOR] & res).Value
            Application.EnableEvents = [COLOR=Royalblue]True[/COLOR]
            [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
        
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]
 

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
Try this:
Copy the code then right click sheet2 tab > select View Code > paste the code.

Code:
[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)

[COLOR=Royalblue]If[/COLOR] Target.Cells.CountLarge <> [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Range([COLOR=brown]"A:A"[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        [COLOR=Royalblue]With[/COLOR] Sheets([COLOR=brown]"Sheet1"[/COLOR])
        
        [COLOR=Royalblue]Dim[/COLOR] res
        res = Application.Match(Target.Value, .Range([COLOR=brown]"A1"[/COLOR], .Cells(Rows.Count, [COLOR=brown]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp)), [COLOR=Royalblue]False[/COLOR])
            [COLOR=Royalblue]If[/COLOR] IsNumeric(res) [COLOR=Royalblue]Then[/COLOR]
            Application.EnableEvents = [COLOR=Royalblue]False[/COLOR]
            Target.Offset(, [COLOR=crimson]1[/COLOR]).Resize(, [COLOR=crimson]3[/COLOR]).Value = .Range([COLOR=brown]"B"[/COLOR] & res & [COLOR=brown]":D"[/COLOR] & res).Value
            Application.EnableEvents = [COLOR=Royalblue]True[/COLOR]
            [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
        
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]
This worked for the example sheet I provided. How would you change the code to where it grabs only specific cells (i.e, B and D but not C)?
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
This worked for the example sheet I provided. How would you change the code to where it grabs only specific cells (i.e, B and D but not C)?
Change this:
Target.Offset(, 1).Resize(, 3).Value = .Range("B" & res & ":D" & res).Value

to this:
Target.Offset(, 1).Value = .Range("B" & res).Value
Target.Offset(, 2).Value = .Range("D" & res).Value
 

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
Awesome. Thank you for all the help. I got it to work for everything I needed!
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
You're welcome, glad to help, & thanks for the feedback.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,369
Messages
5,486,450
Members
407,547
Latest member
Sankarasrinivas

This Week's Hot Topics

Top