Transposing single row to multiple columns between workbooks

[crhoads]

Hello everyone!

I work for a preschool and I developed a spreadsheet to monitor our assessments. I have one spreadsheet that is an overall glance at a classroom and then another that is for each individual child. I need an easier way to transfer the data from the classroom report to the individual report. The data is in a single row for each child on the classroom report, but is in seven different sections on the individual report. To make it more difficult, the spreadsheets are conditionally formatted to three different age groups in order to color code certain scores. So there are three different sheets for each age group for the individual report, and three different types of formatting on the rows for the classroom report. Right now I am literally copy>paste>transpose each individual section for each child, which is going to take me weeks. I am attaching a sample of the classroom workbook and a sample individual workbook. You can see in the individual workbook in red where the data needs to go...there are four different columns and data will be entered four times a year from four separate classroom workbooks. If anyone has any ideas, please let me know! I am not very verbose when it comes to macros and VBA at all, I know there is a way to do this but I have no idea where to start!

[arlu1201]

There are too many colors here

It will be good if you explain clearly what needs to go where.

Do you want the macro to open each student's file which is maybe stored in one folder and then perform the actions required?

[crhoads]

Yes, lots of colors! It's a little hard on the eyes, haha. Each cell is conditionally formatted to color-code itself depending on whether the score for that questions falls below, meets, or exceeds expectations for that age group.

Each row of data on the classroom workbook corresponds with a child's scores. Each set of numbers needs to go in a section on the individual workbook. Each child will have either a different sheet in one workbook or a different workbook, I am not sure which would be easier. Right now I am just doing separate workbooks for each individual child. So the data corresponding to columns I-Q (numbers 1a-3b in the header row) would need to be transposed into the Social-Emotional section of the individual report, and so on and so on.I am not quite sure where to even start with this, because there is just so much stuff to be transposed into so many different sections! I also have to do this four times a year -- the second set of data would go into the column labeled "2," the third into the "3," etc. It would be wonderful if it could open up each child's workbook and transpose the data onto it.