Get sheet name for copy from a drop down

[LannyN]

I am trying to automate processing a huge mailing list into manageable chunks. I have a workbook with 11 available sheets (mail routes) for copy destinations. I have dynamically build a dropdown list comprised of sheets that are "empty" and available to hold a new list segment. My problem lies in trying to convert the drop down value, which is the same as the sheet name, to make it work as follows where CpySht is the value from the drop down.

"Set SelectedRoute = ThisWorkbook.Sheets(CpySht)"

I've tried defining it as a range, concatenating it with ("" & CpySht), and a couple of other methods, but I keep getting an object variable error message.

Any ideas?

Thanks

 

[davesexcel]

if CpySht is a string variable then
there should be no quotes in that line
If the dropdown is an actual combobox then it would be ThisWorkbook.Sheets(combobox1)
If data validateion then if would be the range of the data validation
ThisWorkbook.Sheets(range("A1").value)