VBA Error when Macro Runs

[jamesemaj89]

Hi all,

I've been working on a spreadsheet that generates feedback for students based on how they perform on different questions in an end of topic test (see attachment).

There is a macro that, when run, prints off an individual sheet for each student using iterations. This is the worksheet called 'Question Analysis' and there is a dropdown box at the top where the name of the student can be changed which updates the sheet with details for each student on the 'students' worksheet. It is triggered when on the question analysis sheet, the analysis menu button is clicked and then export to pdf/powerpoint/excel is selected. I've left the test scores in the spreadsheet but replaced student names with numbers for GDPR reasons.

The idea is that when this macro runs, it creates a document with each of these feedback sheets exported to it. The problem is coming from one of the sheets seemingly becoming duplicated which causes the macro to hit a wall and stop working, it doesn't get on to the next record and then doesn't run the code to export and then delete the extra sheets that it creates to do this.

The smaller question is if this is just a stupid code error that is an easy fix for someone more experienced - the second is if there is an easier way of doing the same thing that isn't quite so temperamental?

Any help would be greatly appreciated!

Thanks,

James

[Greg M]

Hi there,

Your code is crashing because you are creating worksheets for students whose names are not listed (i.e. blank cells) in the Range A6:A204 on the Students worksheet, and the routine is trying to assign blank values as the names of the new worksheets. You are also including the total number of students (Cell A206) as a student name and creating a corresponding worksheet.

Without studying all of your code in detail I have rewritten your "Students" and "Duplicate" routines as follows:

Please Login or Register  to view this content.

I have run the "Output Analysis to Excel" routine in the attached workbook with up to 150 students listed, and the application worked correctly.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[jamesemaj89]

Hi there,

Thank you so much for this! Is it as easy to fix the export to pdf function? That's the one that doesn't seem to work still and is the one that my staff prefer to use.

I think maybe its somewhere in here that the error lies?

Please Login or Register  to view this content.

One of the other things i've noticed is that as soon as I put the names back in it bugs out, for some reason creating a duplicated sheet for the same student. I've tried ordering the students differently and deleting the ones that it gets stuck on but no luck, it just gets stuck on a different one.

Thanks again for your offer to help, much appreciated.

James

[Greg M]

Hi again,

Take a look at the attached version of your workbook and see if it does what you need.

The workbook contains 150 student names derived from a random name generator, and both the "Export To Excel" and the "Export To PDF" features work correctly for this number of students.

I've rewritten some of your routines as follows:

Please Login or Register  to view this content.

Some of the changes above will make your code run appreciably faster (e.g. deleting student worksheets in bulk rather than individually, not setting the page layout of each individual student worksheet etc.)


Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

[jamesemaj89]

Amazing, thank you - this has worked well.

One last thing which you might be able to help with - When exporting to pdf it is running very, very slowly and I think this is probably unavoidable on an older machine. Is there a way of speeding this up? Would it be quicker to export to word instead?

Either way I'm extremely grateful to you for your help, thank you once again.