Copy range based on dropdown

Question

A dropdown populates B2 of Sheet1 with a value. I need to find this value in Sheet2, and copy a range starting from that cell, to 5 columns right and 18 rows down. This value copied range is always pasted to I3:M20.

For example, if the value from the dropdown is found in A75 then range A75:E92 should be copied to I3:M20.

Thanks.

Answer 1

If you want it to happen automatically when the value changed you need to use Sub Worksheet_Change as mentioned by @donPablo in the comments. Verify that the cell that was changed was the cell of interest, then find the value from B2 on Sheet2. You can use Resize to get the larger area you're interested, and then copy it where you want it.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Address = "$B$2" Then
        Exit Sub
    End If
    Dim ToFind
    Dim Found
    ToFind = Sheets("Sheet1").Range("B2").Value
    Set Found = Sheets("Sheet2").Range("A1", "E300").Find(ToFind, LookIn:=xlValues)
    If Not Found Is Nothing Then
        Found.Resize(18, 5).Copy
        Sheets("Sheet2").Range("I3").PasteSpecial (xlPasteValues)
    End If
End Sub