Excel 2007 - Saving multiple worksheets as individual PDFs

[tommy.vickers]

Hello,
I want to put together a workbook that all references the same data source so I only have to update once, but breaks it down into 25-30 individual worksheets. These sheets would be localized to specific regions and I want to be able to quickly and efficiently save each one as a PDF on a regular basis (probably weekly) with the name of the worksheet as the file name so that they are easily sorted and referenced for who they go to. I'm figuring this will probably require some VBA work, but I'm not good in VBA at all. It's my one big weakness in Excel work that I need to remedy.

I've found this link which gets me started in moving each sheet to its own file, I just need to figure out if there's a way to automatically take it from an Excel file to a PDF.

On a completely seperate note, it would be awesome to be able to automate an e-mail out to each person with their file/s, but that might be something tackle another time.

Any help would be greatly appreciated! Thanks in advance!

 

[bobhc]

Good day and welcome to the forum if you have not already done so, please read this link as it will help you in the use of the forum.

http://chandoo.org/forum/threads/new-users-please-read.294/

As to your question

Save as -> Save as Type -> PDF

.

 

[tommy.vickers]

Yes, I use that on an individual basis, however since this is a report I'm looking at running regularly the time required to do so with 25-30 individual worksheets just doesn't make it feasible. The article I linked has a VBA code that will save each of the worksheets into an individual file, but it saves them as an Excel file. If I had the right version of Acrobat I could probably do a batch conversion, however, since I don't have access to that through work I'm looking to see if there's another option.

Thanks for your help!

 

[Deepak]

Is that what you are looking for!!!

Option Explicit

Sub Save_As_PDF()
Dim sFile As String, Mypath As String, ws As Worksheet

Mypath = "C:\Users\dEEPAK\Desktop\" 'Chnage
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
    sFile = "_" & ws.Name & ".pdf" 'change
    ws.Activate
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Mypath & sFile, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True
Next
Application.ScreenUpdating = True
MsgBox "Done"
End Sub

 

[tommy.vickers]

Is that what you are looking for!!!

Option Explicit
 
Sub Save_As_PDF()
Dim sFile As String, Mypath As String, ws As Worksheet
 
Mypath = "C:\Users\dEEPAK\Desktop\" 'Chnage
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
    sFile = "_" & ws.Name & ".pdf" 'change
    ws.Activate
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Mypath & sFile, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True
Next
Application.ScreenUpdating = True
MsgBox "Done"
End Sub

Excellent, this is perfect! Thank you very much!