Pasting Data Based On Drop Down
Results 1 to 6 of 6

Thread: Pasting Data Based On Drop Down
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2019
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Pasting Data Based On Drop Down

    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
    A B C D
    1 Number Animal Food Drink
    2 1 Cat Dry Milk
    3 45 Dog Wet Water







    Sheet 2
    A B C D
    1 "Drop Down Here" Auto Pop Auto Pop Auto Pop
    2 "Drop Down Here" Auto Pop Auto Pop Auto Pop

    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.

  2. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,900
    Post Thanks / Like
    Mentioned
    31 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Pasting Data Based On Drop Down

    Try this:
    Copy the code then right click sheet2 tab > select View Code > paste the code.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Cells.CountLarge <> 1 Then Exit Sub
        If Not Intersect(Target, Range("A:A")) Is Nothing Then
            With Sheets("Sheet1")
            
            Dim res
            res = Application.Match(Target.Value, .Range("A1", .Cells(Rows.Count, "A").End(xlUp)), False)
                If IsNumeric(res) Then
                Application.EnableEvents = False
                Target.Offset(, 1).Resize(, 3).Value = .Range("B" & res & ":D" & res).Value
                Application.EnableEvents = True
                End If
            
            End With
        End If
    End Sub

  3. #3
    Board Regular
    Join Date
    Jun 2019
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pasting Data Based On Drop Down

    Quote Originally Posted by Akuini View Post
    Try this:
    Copy the code then right click sheet2 tab > select View Code > paste the code.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Cells.CountLarge <> 1 Then Exit Sub
        If Not Intersect(Target, Range("A:A")) Is Nothing Then
            With Sheets("Sheet1")
            
            Dim res
            res = Application.Match(Target.Value, .Range("A1", .Cells(Rows.Count, "A").End(xlUp)), False)
                If IsNumeric(res) Then
                Application.EnableEvents = False
                Target.Offset(, 1).Resize(, 3).Value = .Range("B" & res & ":D" & res).Value
                Application.EnableEvents = True
                End If
            
            End With
        End If
    End Sub
    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)?

  4. #4
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,900
    Post Thanks / Like
    Mentioned
    31 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Pasting Data Based On Drop Down

    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

  5. #5
    Board Regular
    Join Date
    Jun 2019
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pasting Data Based On Drop Down

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

  6. #6
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,900
    Post Thanks / Like
    Mentioned
    31 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Pasting Data Based On Drop Down

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •