The following code may do the task for you:-
Application.ScreenUpdating = False
Dim MySelection As String
If Range("A2") <> "" Then
MySelection = Range("A2")
Sheets(MySelection).Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
As I don't know how your actual work book is set up or what exactly you want to transfer to each individual sheet, I have made the following assumptions:-
- Your data starts in Row 4 with headings in Row 3.
- Your drop down list is in cell A2.
- You wish to transfer all data from sheet1 (which I assume to be your Input sheet) to the selected individual sheet.
- You want the "used" data in sheet1 cleared once it is transferred to the selected individual sheet.
You will have to let us know exactly how your work book is set up so it would be a good idea to upload a sample of your work book to a free file sharing site such as DropBox, ge.tt or SpeedyShare then post the link to your file back here. Be careful with any sensitive data and show us your inputs and expected results.
Following is the link to my test work book which is set up based on my assumptions above:-
Select a sheet from the drop down in cell A2 and then click on the Find Sheets button to see the code at work.
Once the data is transferred, the code will take you directly to the selected sheet.
Let us know what you think.