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

[bruwer]

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

[royUK]

Which version of Excel are you using? This should be in your user profile to speed up answers.

If earlier than 2007 then which pdf software?

[Bob Phillips]

I use PDFCreator which is free

Dim PDFJob As Object 'PDFCreator.clsPDFCreator
Dim sh As Worksheet
Dim Filename As String

    Set PDFJob = CreateObject("PDFCreator.clsPDFCreator")

    With PDFJob
    
        If .cStart("/NoProcessingAtStartup") = False Then

        '    ThisApp.Error "Can't initialize PDFCreator."
            Exit Sub
        End If

        For Each sh In ActiveWorkbook.Worksheets
        
            Filename = sh.Name

            .cOption("UseAutosave") = 1
            .cOption("UseAutosaveDirectory") = 1
            .cOption("AutosaveDirectory") = "C:\temp"
            .cOption("AutosaveFilename") = Filename & ".pdf"
            .cOption("AutosaveFormat") = 0    ' 0 = PDF
            .cClearCache
        
            sh.PrintOut copies:=1, ActivePrinter:="PDFCreator"
        Next sh

        'Wait until the print job has entered the print queue
        Do Until .cCountOfPrintjobs = 1
    
            DoEvents
        Loop
    
        .cPrinterStop = False
    
        'Wait until PDF creator is finished then release the objects
        Do Until PDFJob.cCountOfPrintjobs = 0
    
            DoEvents
        Loop
    
        .cClose
    End With
    
    Set PDFJob = Nothing

[bruwer]

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?

[Bob Phillips]

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.

[bruwer]

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?

[Bob Phillips]

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