MACRO to print multiple sheets to multiple PDF's with different names

Hi

I use Excel 2003. I also currently use PDF reDirect v2 (free version). I'm happy to use any other free PDF creator if necessary. If cost effective, I will also buy a full version of a PDF creator.

I have a spreadsheet containing about 95 sheets. The first 5 sheets contains calculations. Sheets 6 to 95 currently contains statements and are named 1001, 1002, 1003 up to 1090.

Please note that monthly new sheets might be added. For example, next month there may be new statements (sheets) named 1091, 1092 and 1093.

I would like to create a MACRO that:

1. prints sheet 1 (named 1001) to a pdf doc
2. names the pdf doc "1001"
3. saves the pdf doc to the same location every time (e.g. temp folder)
4. prints sheet 2 (named 1002) to a pdf doc
5. repeats steps 2 and 3 for sheet 2
6. repeats steps 1 to 3 for all the sheets up to the end of the spreadsheet.

Could anyone please assist? This would save me hours every month. Currently I have to create a pdf for each page manually.

I can post an "example spreadsheet" if required?

Thanks!

Bruwer

I use PDFCreator which is free

Please Login or Register  to view this content.

Hi

Thanks for the reply.

I did put the versions in my post. I use Excel 2003 and PDF reDirect v2 (free version).

Thanks!

Would you recommend I download PDFCreator and use your code?

I know, that is why I didn't ask you.

I would suggest that yes. Looking at PDF reDirect it seems that it can interact with VBA, but I have never used it.

It runs, but:

If, for example, there are 3 sheets, named sheet1, sheet2 and sheet3:

It prints sheet1, then sheet2 and then sheet3.

It only saves sheet3 though. It does save the file under the correct folder and correct name for the last sheet (or sheet3).

Any reason why the others are printing, but not saving?

I have this on another machine, let me try it and get back to you.

Thanks. I await your reply.

Just to let you know that I am completely knew to VBA and have no prior experience. The MACRO does run, so there are no compiling errors. But I might have made some other silly error?

To compile the MACRO using your code, I did name the macro and included a Sub () at the top of your code and a End Sub at the bottom. Hope this is right?

Thanks again.