Macro para acomodar dato de celda, en otra hoja en la misma columna

Buenas noches, solicito ayuda para crear una macro que acomode el valor de la celda, en otra hoja, en la misma columna, pero con id en la fila, es decir, cada dos meses, debo extraer de un ciento de archivos, las calificaciones de todos los alumnos que cursaron ese periodo, en un concentrado en excel, las filas en este caso, es el nombre del alumno, y la columna la materia que cursó, que puede ser de una hasta siete, una vez hecho el listado, acomodo alfabéticamente, el nombre de los alumnos, para manualmente ir acomodando las calificaciones en una sola fila y eliminar las demas filas sin calificaciones del mismo alumno, posteriormente acomodo en un megaconcentrado, las calificaciones de cada alumno en su materia correspondiente, si ya tiene valor en una materia, lo sobreescribo, si no, obviamente lo agrego, la idea es tener el historial académico del chico, pero se ha vuelto muy pesado, y se van a ir agregando mas alumnos conforme pase el tiempo, por lo que estoy buscando una manera de hacer una macro que se encargue de realizar el trabajo, y minimizar el error humano.

Cabe destacar que son cinco carreras, con cerca de 60 materias cada una, lo que lo vuelve un trabajo bastante difícil.

Obviamente no puedo copiar y pegar, pues celdas con valor vacío, sobreescribirian las calificaciones existentes, el uso de fórmulas viene siendo el mismo problema, por lo que solo queda una macro, no se ha creado una base de datos, pues es lo mismo, preparar los datos, para importar a la BD, ademas que no es muy alto el porcentaje de personas que sepan usar algo mas que excel, por lo que estoy buscando alguien que pueda apoyarme en la creación de la macro.

De antemano gracias.