You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
Hi folks,
Anyone out there able to tell me if this is possible and if so code it for me please?
I've got a spread sheet that analyses student data for an exam-
What I want to do is give them an individual print out.
The macro-
I need it so I can select an area on a sheet. Then click crtl u and it print the left most ten columns worth of what has been selected, then hide the tenth column, then print again, then hide the tenth column and print again stopping when the last student has move across and been printer (the left 9 columns will have details of questions etc, then the tenth one has their name at the top and all there scores underneath.
There are three different blocks on the one sheet, so I need it so I can highlight whatever I want to print and press crtl u.
Does that make sense?
Is it even possible?
Any help would be amazing! and 200 year 11 students would be very grateful after their mock exams too!
Thanks in advance
Paul
It sounds like you have student data in columns moving across the page?
Have you thought about using Mail Merge?
Mail Merge +1
Create either a word doc or emails to send out directly.
Cheers guys,
I can use a mail merge, however I would really like this in a macro on a spreadsheet. There's lots of editing of the sheets that goes on and I want to be able to just edit sheets and re-print without going to a word document and editing it.
Last time I mail merged for this type of work, sending the colour of the boxes was a massive pain in the backside too (they rate between green at 100% and red at 0%).
Thanks for the input.
I've got a similar macro that is used but in a horizontal plane, but I can't edit it properly to do what I want with vertical hiding of columns.
In that case could you use a Pivot Table or Report and then use a macro to change the filter criteria?
Can anyone tell me if this is do-able?
It's the selected print area etc that makes me unsure?
You'd be better not trying to do it by hiding columns as that would mean you have to reset the print area each time as well.
I have a similar thing setup for some population health data of all thing, but it's a case of building a 'presentation' page in one worksheet, using vlookups to populate that sheet from your list, then using VBA to flick to the data page, increment by one down the list, flick back to the presentation page, print as PDF, then loop until you're finished.
You’d be better not trying to do it by hiding columns as that would mean you have to reset the print area each time as well.
Single command in VBA, so no more difficult than hiding a column....
As Pyro says, set up a sheet for the presentation layer then you can loop through the student name, bring all the right fields through and then print using something like this?
https://powerspreadsheets.com/excel-vba-print/
Single command in VBA, so no more difficult than hiding a column….
Yes, but an extra and unnecessary one if you set up your presentation separately. I think I probably picked up the basis of what I was describing from the link mikewsmith's linked above. May not work for everyone, but I always find it simpler to get a static-ish 'output' looking like I want it then drop in data.
Thanks for the responses. I'm afraid they've gone over my head though!
Mikewsmith and pyro are suggesting creat a new sheet that does the bit about ten rows etc based on the students name.
Then make a macro to change the name and send all the way to the end of the list.
If you wanted a macro to do exactly what you want I'd just record a macro of you doing it a couple of times then edit it to add the repetition.