No Macro experience, need to select from drop down list and copy/paste to new book... any help?

dropkickweasel

New Member
Joined
Feb 2, 2014
Messages
24
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
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,719
Members
414,401
Latest member
grenona2020

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top