Appending student data from multiple sheets in chronological order on another sheet

Hi Excel Programmers:

Described here is functionality that a small school would like added to an Excel app for tracking student grades and progress. The gradebook computes both objective, academic grades as well as more subjective, behavioral grades, and is used to track the progress of kids with severe behavioral, emotional, and learning disorders.

In short--teachers would like Excel to create new sheets patterned after “Progress Report Template” for all students marked “y” and, for a certain date range, copy the students’ comments entered in the “Period” sheets to each student’s newly made progress report sheet. Any coding help would be greatly appreciated.

The current gradebook application started 6 years ago and has ballooned into the attached. Thanks to the number of forum participants/programmers for making it work. Any additional help would be greatly appreciated.

Here are worksheets involved:

Sheet named “Students” in which last and first names of students are imported into cols A and B. On this sheet are also two Microsoft Date Time Picker controls for selecting starting and ending report dates.

7 worksheets named Period 1, Period 2,…, Period 7 (for 7 different school classes). A student may be in 1 or more period. If a student is in multiple periods, he/she is Not necessarily entered on the same row.

Worksheet named “Progress Report Template” to pattern reports after for students in a teacher’s caseload.

Here is the setup of the 7 worksheets named Period 1, Period 2,…, Period 7:

Last and first names of students are in columns B and C on every ODD row, starting on row 13.

Dates are inserted in row 12; each day’s date is in a separate column. There are other headings along this row, not just dates, but the date columns are entered consecutively.

Comments about a student’s work that period are typed in the date columns, but 1 row Below which the student’s names have been inserted (so the comments are on every EVEN row).

Here is the desired functionality:

Teachers would like to go to the sheet named “Students,” select starting and ending dates from the Microsoft Date Time Picker controls (E3 and E5), click a blank cell next to a student’s name (to select that student to report on), say, a cell in col D, and then have Excel do the following:

1. Store the last and first names of the student from cols A and B as variables.

2. Store the starting and ending dates from E3 and E5.

3. Create a copy of the worksheet named “Progress Report Template” in the workbook.

4. Rename the copy of “Progress Report Template” with the student’s first and last names.

5. In the student’s new report worksheet, input his/her last name in cell B1 and his/her first name in B2.

6. In the student’s new report worksheet, insert the starting date in B6 (or a row below any existing data).

7. Here’s the crazy part – scan Period 1 for the student’s last and first names (would be in cols B and C on the "Period" sheets). If found, do the following:

8. Copy the typed comment (if any) in the cell at the intersection of 1 row below the student’s names (cols B and C) and the column with the date heading in row 12 that matches the starting date.

9. Return to the student’s report worksheet and insert the starting date in cell B6 and the corresponding comment in cell C6 (or a row below existing data).

10. Then, go to and scan Period 2 and do the same if the student is present—copy the comment at the intersection of 1 row below the student’s names and the column with the matching date in row 12, return to the student’s report worksheet and insert the date and comment [a row below the previously used row].

11. Repeat this for each Period’s sheet.

12. AND—for a range of dates (from the Microsoft Date and Time Pickers in the Student’s sheet)—repeat this for the next consecutive date—scanning the Period worksheets, copying the comments for the students on those days, then copying and listing them on the student’s report sheet.

13. Lastly, set the newly inserted dates and corresponding comments as the new print range (everything above row 5 will be the page’s heading).

Don’t want very much do I? I realize this is very complex. Again, any help on this would be super appreciated. Thanks.

Thanks JBM444 for your efforts. I will test your contributions tomorrow and repost.

The school is rather small and teachers do not have too high of a caseload - 10 students or so to report on. Therefore, some repetitiveness is OK. Most reports on indiv. students will be done on a weekly or bi-weekly basis.

Here is another obstacle - memo fields in the report sheets. See, most of the time during school, teachers will only type short notes or keywords in the comments cells (green colored), and then need to elaborate on their comments later. However, if Excel limits the amount of characters in cells (if a teacher decides to type a paragraph for a certain date), this may pose problems printing the data. Therefore, I suppose there may be a way to export everything into a Word document; never tried it.

In reality, this would be much better suited to a database app, but converting everything would just take too long.

Thanks again.