Loop Save-as PDF through workbook

[laskisperj]

I'm currently using the following macro to save an active worksheet as a pdf:


Sub SaveAsPDF()

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Documents and Settings\My Documents\" & Range("a1").Value & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True


End Sub



I want this to loop through each of the worksheets in the worksbook to create a different pdf file for each worksheet, but keep getting errors...i think with "Activesheet"

I tried using:

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets

 

[JasonC71]

The following does what you ask for me in excel 2007. It will create pdf's of each worksheet and then return the user to the original active sheet. It is probably not the most efficient answer, as I am new to VBA, but it does work.

Sub PDFENTIREWORKBOOK()
 
Dim ws As Worksheet, aws As Worksheet
On Error GoTo Err
'set active worksheet to return to later
Set aws = ActiveSheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\Jason\Documents\" & ws.Range("A1") & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False
Next ws
aws.Activate
Application.ScreenUpdating = True
Exit Sub
Err:
Application.ScreenUpdating = True
MsgBox ("Failure")

Jason

Edit: Be sure to change the filepath!!