Save all sheets to PDF

[mrmrf]

Hi,

Looking for VBA to save all sheets to PDF. Currently i have the below, but only saves the active sheet.

Thanks.

Private Sub CommandButton8_Click()
Dim sPath As String
Dim sFile As Variant
Dim ws As Workbook


On Error GoTo ErrHandle
sPath = ThisWorkbook.Path & "\" & ThisWorkbook.Sheets("Sheet1").Range("G10") & " - Test name"


sFile = Application.GetSaveAsFilename _
    (InitialFileName:=sPath, _
Filefilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and File Name to Save")
If sFile = "False" Then
    MsgBox ("Please Choose a File Name")
Exit Sub
End If


Me.ExportAsFixedFormat _
    Type:=x1typePDF, _
    Filename:=sFile, _
    Quality:=q1qualitystandard, _
    Includedocproperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
Exit Sub
ErrHandle:
MsgBox ("Document Not Saved")


End Sub

 

[DanteAmor]

Hi,

Looking for VBA to save all sheets to PDF. Currently i have the below, but only saves the active sheet.

Thanks.

Private Sub CommandButton8_Click()
Dim sPath As String
Dim sFile As Variant
Dim ws As Workbook


On Error GoTo ErrHandle
sPath = ThisWorkbook.Path & "\" & ThisWorkbook.Sheets("Sheet1").Range("G10") & " - Test name"


sFile = Application.GetSaveAsFilename _
    (InitialFileName:=sPath, _
Filefilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and File Name to Save")
If sFile = "False" Then
    MsgBox ("Please Choose a File Name")
Exit Sub
End If


[B][COLOR=#0000ff]ActiveWorkbook[/COLOR][/B].ExportAsFixedFormat _
    Type:=x1typePDF, _
    Filename:=sFile, _
    Quality:=q1qualitystandard, _
    Includedocproperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
Exit Sub
ErrHandle:
MsgBox ("Document Not Saved")


End Sub

Use ActiveWorkbook to save all sheets.

 

[mrmrf]

Perfect, thank you.

What about another scenario where i want to save all sheets, if not a certain sheet name?

 

[DanteAmor]

Perfect, thank you.

What about another scenario where i want to save all sheets, if not a certain sheet name?

I did not understand, could you explain what you need?

 

[mrmrf]

Hi DanteAmor.

I would like the code to only save the sheet if it isn't a certain name.

For example, if i had a document with "sheet1", "sheet2", "sheet3", "sheet4" etc, and i want to save all sheets but not the sheet with the name "sheet3"

Thanks

 

[DanteAmor]

Hi DanteAmor.
I would like the code to only save the sheet if it isn't a certain name.
For example, if i had a document with "sheet1", "sheet2", "sheet3", "sheet4" etc, and i want to save all sheets but not the sheet with the name "sheet3"
Thanks

Try this

Dim sPath As String
Dim sFile As Variant
Dim ws As Workbook




On Error GoTo ErrHandle
sPath = ThisWorkbook.Path & "\" & ThisWorkbook.Sheets("Sheet1").Range("G10") & " - Test name"




sFile = Application.GetSaveAsFilename _
    (InitialFileName:=sPath, _
Filefilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and File Name to Save")
If sFile = "False" Then
    MsgBox ("Please Choose a File Name")
Exit Sub
End If


[COLOR=#0000ff]Sheets(Array("Sheet1", "Sheet2", "Sheet4")).Select[/COLOR]
[COLOR=#0000ff]ActiveSheet.ExportAsFixedFormat _[/COLOR]
    Type:=x1typePDF, _
    Filename:=sFile, _
    Quality:=q1qualitystandard, _
    Includedocproperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
Exit Sub
ErrHandle:
MsgBox ("Document Not Saved")




End Sub