Excel VBA paste range from dropdown list to named sheet matching dropdown result

[Jake84]

Hi all,
I am a little stuck on an excel VBA problem with copying and pasting data using a drop down list.

I have an excel workbook with a worksheet (“LoadData”) that has all the databased data.
On another worksheet (“LookUp”), I have a dropdown (data validation) in cell A1. This dropdown is a list of names. When you select a name from the dropdown list, it updates a certain range on the LoadData worksheet (“A2:AA2”) by pulling in data from the database sheet using formulas.
I am trying to create an excel macro that will loop through the dropdown list one at a time, copy the data range for each name and then paste the data range in the worksheet that corresponds to the name that is selected in the dropdown list (the worksheets are already named based on the list). I need it to paste the data (values only) on the next empty row on the specific worksheet.

Alternatively, i could have a macro for each name and copy/paste data that way, but was hoping for one click rather than one click for each name

Thanks in advance for your help.

 

[My Aswer Is This]

You said:

This dropdown is a list of names

What are these names?

Are these sheet names?

So are you wanting to run a script to do something on all or most all of your sheets?

Are you by chance wanting to copy all the values from A2 to AA2 ??

From all your sheets and paste these values in another sheet named

LoadData?


Please answer all question.​

_{^{<strike>
</strike>}}​

 

[Jake84]

Thanks for the reply.. see below answers to your questions.

You said:

This dropdown is a list of names

What are these names?

Are these sheet names?
The names are of people - a first and a last name. Each person has a specific worksheet named after them (which I want the data to be pasted into).

So are you wanting to run a script to do something on all or most all of your sheets?
Each person has a worksheet named after them. When you select the person from the dropdown, this updates the data in A2-AA2 (specific to the person). I am hoping to get this data then copied and pasted into their worksheet on the next available Row.

Are you by chance wanting to copy all the values from A2 to AA2 ??
Yes, copy all the data.

From all your sheets and paste these values in another sheet named

LoadData?
The Data and dropdown list is all in the sheet named "LoadData". I am wanting to copy the data from worksheets("LoadData").Range("A2:AA2") and paste in the next available row in the worksheet that corresponds to the persons name that is selected.


Please answer all question.​

_{^{<strike>
</strike>}}​

 

[Jake84]

Sorry.. the last question you asked i answered wrong.
The "LoadData" sheet is a database with data coming in from a number of sources containing information on a large number of people.
The worksheet "LookUp" has the dropdown list to select from a number of people in a group. By selecting the person from the dropdown, the "lookup" worksheet is updated with the person's information in cells A2-AA2.
The LoadData sheet is just a database sheet that contains the original information for every person in no particular order. The lookUp sheet is used to sort into relevant information (based on person selected in the dropdown), and the individual named worksheets (by person) hold the information for these people.

Hope that makes sense.

 

[Jake84]

Currently i am manually selecting the names from the drop down list and applying my "CopyData" Macro see below. This is working fine, however, i am trying to automate this loop so i do not need to manually click each persons name from the dropdown then click the copyData button each time.

Range("A2:AA2").Select
Selection.Copy
Worksheets(Worksheets("LookUp").Range("A1").Value).Activate
Worksheets(Worksheets("LookUp").Range("A1").Value).Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("LookUp").Select
Range("A3").Select

 

[My Aswer Is This]

Jake 84

I'm not sure how to do things the way you want.
You have a list of sheet names in Range("A1") Data Validation list.
And first time select first value in list and second time select second item in list and so on.

I'm not knowledgeable about how to do that.
Now if you had one sheet name in A1 and another sheet name in A2 and another in A3

I could work with that.

Not sure why you want things done the way you have decided

Maybe someone else here at Mr. Excel will be able to help you.

I will continue monitoring this thread to see what I can learn

 

[Jake84]

Thanks for the reply and offer to help. yes it is as you describe.
It needs to be like this to import the data into the specific named sheets, and by selecting the validation list, it updates the data that needs to be copied.

thanks again.