Macro for conditionally moving info from one worksheet to others

scavengertype Posts 1 Registration date Thursday June 2, 2016 Status Member Last seen June 2, 2016 - Jun 2, 2016 at 08:20 PM
Hello and thank you up front for reading my question.

This feels complicated but i have only minimal experience with macros and VBA.

We start with a workbook with 4 worksheets: worksheet 1 "Home", worksheet 2 "Sheet 2", Worksheet 3 "Sheet 3", worksheet 4 "Sheet 4".

Worskheet 1 "Home" has a cell "C9" with dropdown box of names (which populates another cell "C11" with VLOOKUP function) and a cell "C13" with a second dropdown box (which populates another cell "C15" with VLOOKUP function)...
(ref figure 1)

...I would like to create a macro (button activated on "Home" worksheet) that will find the worksheet (2, 3, or 4) with the column headed by the name seen in "C9" from the "Home" worksheet, then place the information from cells "C13" and "C15" into subsequent (transposed to side by side cells) rows under that column name.

Ex: The first time the button is pressed and "same team" from "C:13" and "5" from "C:15" copies over to worksheet 3 "Sheet 3" under Julie, they would populate in "N:8" and "O:8", respectively. The next time "Julie" is in "C:9" and the submit macro button is pressed, the info from "C:13" and "C:15" are copied over to worksheet 3 "Sheet 3" under Julie in cells "N:9" and "O:9", respectively. And so forth and so on depending on the name that is in "C:9" on the "Home" sheet.
(ref figure 2)

If another function could be added to the end of the macro to update the total points box just under the names (acorss the three sheets) that would be the sum of all the points in (in this case) column "O", that would be great! Otherwise I can use a simple =SUM(O8:O100) function.

Again, I figure this is pretty complicated and might be a really big ask, but I really appreciate the help. It's a project I'm putting together for a teacher friend of mine to track points for her kids. *Names have been changed*

Thanks again!