Macro to create and compile information

Currently where I work we print out registers to keep track of student attendances. This is done by merging an excel document that contains all the details of the names, days, dates , times, student groups and rooms of each lesson (each row contains the details for one lesson) with a word document containing the appropriate list of students in each specific group along with blank mail merge fields that relate to the excel document.

There are often 100 lessons a week meaning it takes an age to merge all the documents to create the registers.

Instead of this method I'd like the tutor to print off their own register by opening the excel document and clicking a button at the end of the appropriate row that contains the details for the lesson they are teaching. This would then take the details from that row and generate the appropriate register on another worksheet. Importantly it would have to list the correct group of students for each lesson - the names of these groups would be listed on other sheets.

Im guessing macros would be the right way to do this, but I have no real knowledge of them so alternatives would be helpful. Can anyone point me in the right direction? I've included a sample of how it would look with just one lesson in the excel doc.

Any help would be most appreciated.

Thanks

Paste this macro into a new module in the VBA editor ALT F11.

Please Login or Register  to view this content.

I would suggest just having a single button at the top of the sheet which you assign to the macro with the top row frozen so that it is always in view. The code will then use the values from the selected row.

You will need to ensure that the name of your individual course sheet exactly matches the value in column A of the list of courses so that the code knows which sheet to look at.

Hi,

Attached is a starter for 10.

I think you'd be advised to keep a single sheet which contains the Programme and Student names rather than a sheet for every programme, hence I've added the course name against each student name on the Names sheet. It's usually advisable to create a database like this since future developments and reporting are then much easier. Just continue adding programmes and student names.

I've also changed the Register sheet slightly to eliminate the merged cells. We're using Data filtering here and merged cells just get in the way apart from being a pain to work with in other aspects of Excel functionality.

The List of Lessons tab contains two hidden rows 1 & 2. When you put the cursor in the appropriate programme row, (six in this simple example), and click the button, the hidden row 2 is populated with the relevant values from the row chosen. This means that the Register sheet header cells are always linked to the same row 2 cells on the first tab.

Clicking the button will also filter the students names for the programme in question from the 'Names' tab to the Register tab and then print the register.

You may like to consider whether a drop down cell containing all the course names may not be preferable to having to position the cursor in the relevant row before clicking the button. Personally that's the way I would choose.

Hope this gives you some ideas.

Rgds

Hi,

Try the attached.

For some reason that's not immediately apparent to me, extending the filter criteria to cover A1:C2 didn't work as I expected. The Data filter didn't recognise the text "08/09". It was happy with " 08/09", i.e. a leading space, or even "0809" but the "/" character seemed to confuse it. (I'll need to work out why since I've never encountered that before).

So the solution is to use a helper column on the names sheet which concatenates the program, year and group, and use this as the sole criteria. You can of course hide this helper column, (and as an aside helper columns are very useful for circumstances like this and particularly in earlier versions of Excel where SUMIF() is restricted to one condition, unlike XL 2007).

Regards

Hi Richard,

This works really well, thanks.

The 08/09 problem is one we have when entering these numbers, we generally get round it by putting an apostrope before the 0.... Not sure if that's relevant or not...

I've added some drop down lists and changed the layout a little, and it seems to have upset the printing, as it now only prints the lesson info, but not the student names...Ive compared my new saved version to yours but can't work out what ive done....

Apologies in advance...

Hi,

The problem was the range name 'Register'. This is a dynamic range name so that it will automatically adjust for the number of student names listed which will vary. The dynamic range name uses a COUNTA() function within it which counts the number of non blank cells in column A of the Register tab. Originally all the cells in the top header section of 8 rows had some value or other. After you reorganised the headers only A3, A4, A6 & A7 contain values so the range name was 4 rows short. I've added a '+4' in the dynamic range name after the COUNTA() function which now corrects this.

See the attached,

Regards