Thread: PDF multiple sheets as new files

Hello,
I'm pretty new to VBA and was wondering if someone could help me with some code.

I need a macro that will PDF each sheet in a workbook and save each PDFed sheet as a separate file.

The file name for the PDF should be the name of the worksheet.

I have many worksheets in each workbook, hopefully I can just start the macro and have everything else automatic from that point.

Thanks for the help!

Originally Posted by Domski

See attached if you are using Adobe.

Dom

Hi Domski

I am looking for a similar solution. I am using excel 2010. I am new to excel vba and just joined this forum ;-) I have ran your code and it only saves the active worksheet as pdf. I was looking for all worksheets saved into individual pdfs and saved as the worksheet name.

The code I am using is below. Please can you assist me to get this to work for multiple sheets?

I see you are a key contributor and really good at this stuff

Many thanks in advance!

------------------
Sub Create_PDF()

'Dom Hill, July 2010

'This code has only been tested with Adobe Acrobat 6.0 Professional
'For this code to work the Adobe Distiller VBA library must be enabled
'Select Tools...References in the VBA editor and check it from the list

'The code will pdf all the sheets in the active workbook and save them
'to a sub-folder called Saved Files in the directory that this workbook
'is saved

Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String
Dim wsEachSheet As Worksheet
Dim mypdfDist As New PdfDistiller

'define file name and path in subdirectory of file where code workbook
'is stored called 'Saved Files' and picks file name up from a cell
'named as a named range call 'File_Name'

For Each wsEachSheet In ActiveWorkbook.Worksheets

'tempPDFRawFileName = ThisWorkbook.Path & "\" & wsEachSheet.Name

tempPDFRawFileName = wsEachSheet.Name
tempPSFileName = tempPDFRawFileName & ".ps"
tempPDFFileName = tempPDFRawFileName & ".pdf"
tempLogFileName = tempPDFRawFileName & ".log"

'Prints worksheet 'Output' as a pdf, an array of sheets can be printed if required

wsEachSheet.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
printtofile:=True, Collate:=True, prtofilename:=tempPSFileName

mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, ""

Kill tempPSFileName
Kill tempLogFileName

Next wsEachSheet

Set mypdfDist = Nothing

End Sub