Macro to export to pdf

[suedavies]

I have a workbook that calculates times based on certain start time, schedules etc and then using a macro designed sometime ago by a colleague prints all the selected sheets to the default printer - say from rider 1 (lowest number) to rider 10 (highest number) but could be up to 500 sheets, however I would like to amend the macro so it exports the data in one file in a pdf, I've tried just changing the default printer to the microsoft pdf option but that then requires me to name each rider page separately and I'd like them all in one folder to be able to email over - any help would be appreciated I've pasted the code I have below

Thanks

Please Login or Register  to view this content.

[shirleyxls]

You could try to save as pdf instead of printing:

Please Login or Register  to view this content.

This creates a single pdf of all sheets in the workbook. Is that what you needed?

If not, could you attach a small sample workbook for us to review.

[suedavies]

Hi Shirley

Yes I want to save as all of the output to one pdf, I managed to change the code to do this but it was asking me to save each rider timesheet as separate pdf's and I have up to 500, where would I put the code you have quoted above and what do I need to delete from the existing code to get it to work?

Thanks for your time

[suedavies]

attached zip file of workbook

thanks

[shirleyxls]

Hi Sue,

Try taking out the line below your send timecards to printer (I'd just put a single quote mark in front of the instruction while you test it in case you want to put it back!)

I think you will need to put the new "save" code after the Loop ends (just before you save your workbook). From what I can see in the code, the print instruction happens for each separate rider number because it's inside the loop, so if you put the save in there it would create a pdf of the whole workbook every time the rider number changes which is probably not what you want

Give it a try and see how you get on.

Shirley

[bakerman2]

You can keep code until Print-part (can do with a bit of tweaking but is ok for now).
Instead of printing you need to add a new worksheet and copy the PrintArea to it. Repeat this for every rider until finished. Then group all newly created sheets and send to Pdf.
Delete all sheets you just created to have a blank template again.
I'll see if I find the time to put the code together for this later today.

[suedavies]

thanks bakerman2 that would be helpful if you could look a the code, I realise that the coding is probably a bit outdated as it was done in 2002 but didn't want to mess with what has been working for me, I just need to be able to put all the results of the macro into one pdf to send to be printed

Shirleyxls will try yours as well later today

[suedavies]

I have just realised the first code I posted was some that I had altered from the original and had added the following : PrintTofile:=True,

Please Login or Register  to view this content.

[shirleyxls]

Hi Sue,
This wouldn't matter for my suggestion as you can take out the line where you send the file to print; the solution I suggest just saves the workbook in pdf format.

You could give it a try maybe although bakerman2 is doing some code for you anyway.

[suedavies]

Hi Bakerman2, sorry I'm very much a newbie at macros & code can you explain in idiots language what I need to do, when the macro runs I want it to calculate the timecard for all riders and then save the complete output to one pdf file, I fiddled with the code and managed to get it to export to pdf but it was asking for me to name and save each timecard individually, I could do this for a handful but I anticipate having a couple of hundred. Any further help would be most appreciated

sue

[bakerman2]

Sorry for the delay but been really busy at work last few days.
Try example file.
The only thing left to do before running code is adjusting path to save pdf-file to.

Please Login or Register  to view this content.

[suedavies]

thanks Bakerman 2 fantastic

[bakerman2]

You're welcome and thanks for the rep.

[suedavies]

Hi Bakerman2

Sorry to be bothering you again, you kindly helped with the macro code to export my results to a single pdf file which works fab but I have a quick question, on my original code if there was no check time shown it left the cells in the actual timecard as blanks, in the code you gave me it prints zero's, can you explain what I need to change to ensure that if there is nothing to display I get a blank cell rather than a "0"
Kind regards
Sue