I'm trying to copy a column based on my drop down box

[JGode]

Hello,

I am trying to copy a column from Worksheet 2 to Worksheet 1 based on my drop down box on worksheet 1 which is made up of the column headers on worksheet 2.

My drop down box is on worksheet "Drawing Content" in cell B1 and the column headers are on worksheet "Master" and start in E1 and end AU1 but I'd like to potentially add more columns at a later date.

I am a beginner so any help is much appreciated!

Thank

 

[Fluff]

Hi & welcome to MrExcel.
Is your drop down a data validation drop down?
Also where on sheet1 do you want to copy the column?

 

[JGode]

Hello,

Yes, it is a data validation drop down.
I'd like to copy the column to cell to B2

Thanks

 

[Fluff]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Fnd As Range

   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "B1" Then
      Set Fnd = Sheets("Master").Range("1:1").Find(Target.Value, , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then
         Sheets("Master").Range(Fnd.Offset(1), Fnd.End(xlDown)).Copy Range("B2")
      End If
   End If
End Sub

This needs to go in the "Drawing Content" sheet module

 

[JGode]

That worked a treat! Thank you very much.

 

[Fluff]

're welcome & thanks for the feedback