Macro to pull columns of data from multiple worksheets into summary worksheets

Hi there,
I know this is going to be a big ask, but I am hoping someone here can provide some guidance to help solve my issue.

I have an Excel workbook that contains many worksheets. My goal is to pull data from each worksheet and put it into one of two summary worksheets.

More specifically, there are two variables I am interested in pulling, Variable 3 and Variable 7. Each worksheet has a cell (B2) that indicates whether the data belongs to either a control participant or an injured participant. I want to design a macro that will put the data from each sheet into the respective summary sheet, labelled either "Control" or "Injured", based on what is listed in Cell B2. And with each subsequent worksheet, I want the data to be put into the cells adjacent to the previous data in the appropriate summary sheet.

Lastly, I also want there to be a label for each variable, that combines the variable name with the subject's ID number found in Cell B1 for each worksheet.

I really hope this makes sense. I have created a mock-table to give an idea of what each worksheet looks like (I made three), and what I want the summary worksheets to look like (one for control and one for injured).

Below is what I have pieced together thus far (obviously not finished), but I am really just shooting for the stars at this point. Not even sure if I am on the right path, as this is my first foray into Excel Macros.

Please Login or Register  to view this content.

Thanks for any help you can provide!

That is amazing. Thank you so much for putting that together! There's no way I would have figured all of that out.

I was able to get it working with my actual data set, however I would like to figure out what everything means, as using macros seems to be something that will come in handy as I progress with my studies.

Again, thanks for the help!