Save all sheets to PDF

mrmrf

New Member
Joined
Jun 3, 2019
Messages
34
Hi,

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

Thanks.

Code:
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Hi,

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

Thanks.

Code:
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.
 
Upvote 0
Perfect, thank you.

What about another scenario where i want to save all sheets, if not a certain sheet name?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top