If value selected on dropdown then copy range

[excel_2009]

Hi All,

I have a drop down with 4 options, depending on which option is selected the corresponding sheetname would be selected and column A would be copied to the clipboard.

e.g the dropdown values are as follows ebay, amazon, otto, tesco and the sheetnames are the same

Any idea how this would work?

Many thanks!!!

 

[My Aswer Is This]

I assume when you say I have a drop down you mean a Data Validation list
And since you did not say where this drop down is I assume it will be in Range("A1")
If not modify my script.
My script copies from Range A1 to the last value in column A
Not the entire column which would be 1.5 million cells
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab where you have the drop down
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  6/28/2019  12:20:39 AM  EDT
If Target.Address = Range("A1").Address Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
On Error GoTo M
Dim Lastrow As Long
Dim ans As String
ans = Target.Value
Lastrow = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row
Sheets(ans).Range("A1:A" & Lastrow).Copy
End If
Exit Sub
M:
MsgBox "There is no sheet named  " & ans
End Sub

 

[excel_2009]

Wow! this is fantastic, worked perfectly, thank you so much!!!!!!!!!!!!!!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Wow! this is fantastic, worked perfectly, thank you so much!!!!!!!!!!!!!!