dropkickweasel
Board Regular
- Joined
- Feb 2, 2014
- Messages
- 70
Hi,
I've been searching for an answer to this for a while, but unfortunately when I find anything that looks relevant I don't have any macro/VBA knowledge to really understand what I need to do to the code/parts of code to make a macro that does what I want it to do, so I'm hoping someone can help me find/understand the necessary steps.
I have a workbook which records scores from tests and pieces of coursework for a class of students.
On the first sheet I have input basic student data - student numbers, names, target grades.
On the second sheet I input all of their raw data over five different tests/assessments.
Excel then converts their raw scores on the two test papers to a grade, and takes the best two pieces of work (from a possible of six) in each of three coursework assessment tasks.
The third and final sheet allows me to select student names from a drop down list populated from the first sheet, which then populates a formatted sheet for the individual student giving them their scores and grades in each of the tests and assessments along with calculating their strengths and weaknesses and suggesting ideas for improvement. I can then print this out each time I add new data to the second sheet and students have an up to date record of their progress.
What I would like to be able to do is create a macro which:
On the third sheet of the current book, selects a student number from the drop down list;
Copies the current sheet of data and pastes it as a new worksheet in a new workbook, as values only (I don't need to modify this data, it's just for my records), maintaining the source formatting;
Renames the worksheet according to the student name (which would be displayed in one of the cells on the worksheet);
Repeats the action for all students numbers in the drop down list, saving each one as a new sheet in the same new workbook;
Saves the workbook in the same folder with a "name + (date)" format.
If attaching an unpopulated copy of the workbook would be useful to anyone who is able and willing to help, then I'll be more than glad to do so...
Any help/guidance/ideas/suggestions are greatly appreciated.
Thanks,
Ian
I've been searching for an answer to this for a while, but unfortunately when I find anything that looks relevant I don't have any macro/VBA knowledge to really understand what I need to do to the code/parts of code to make a macro that does what I want it to do, so I'm hoping someone can help me find/understand the necessary steps.
I have a workbook which records scores from tests and pieces of coursework for a class of students.
On the first sheet I have input basic student data - student numbers, names, target grades.
On the second sheet I input all of their raw data over five different tests/assessments.
Excel then converts their raw scores on the two test papers to a grade, and takes the best two pieces of work (from a possible of six) in each of three coursework assessment tasks.
The third and final sheet allows me to select student names from a drop down list populated from the first sheet, which then populates a formatted sheet for the individual student giving them their scores and grades in each of the tests and assessments along with calculating their strengths and weaknesses and suggesting ideas for improvement. I can then print this out each time I add new data to the second sheet and students have an up to date record of their progress.
What I would like to be able to do is create a macro which:
On the third sheet of the current book, selects a student number from the drop down list;
Copies the current sheet of data and pastes it as a new worksheet in a new workbook, as values only (I don't need to modify this data, it's just for my records), maintaining the source formatting;
Renames the worksheet according to the student name (which would be displayed in one of the cells on the worksheet);
Repeats the action for all students numbers in the drop down list, saving each one as a new sheet in the same new workbook;
Saves the workbook in the same folder with a "name + (date)" format.
If attaching an unpopulated copy of the workbook would be useful to anyone who is able and willing to help, then I'll be more than glad to do so...
Any help/guidance/ideas/suggestions are greatly appreciated.
Thanks,
Ian
