Pasting Data Based On Drop Down

[ERed1]

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
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Number[/TD]
[TD]Animal[/TD]
[TD]Food[/TD]
[TD]Drink
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]Cat[/TD]
[TD]Dry[/TD]
[TD]Milk
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]45[/TD]
[TD]Dog[/TD]
[TD]Wet[/TD]
[TD]Water[/TD]
[/TR]
</tbody>[/TABLE]







Sheet 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]"Drop Down Here" [/TD]
[TD]Auto Pop[/TD]
[TD]Auto Pop[/TD]
[TD]Auto Pop
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]"Drop Down Here"[/TD]
[TD]Auto Pop[/TD]
[TD]Auto Pop[/TD]
[TD]Auto Pop[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Akuini]

Try this:
Copy the code then right click sheet2 tab > select View Code > paste the 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]

Try this:
Copy the code then right click sheet2 tab > select View Code > paste the 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]

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]

Awesome. Thank you for all the help. I got it to work for everything I needed!

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.