Save multiple pages to PDF

[Peter Muller]

Good afternoon,

I save every page to the right of “Reports” to a PDF file.

I have the following code which you assisted me with before, but it was to save each page individually. I now want to save all the pages to PDF at the same time. Please assist.

'To print sheet and save to PDF

Dim sht_nm As String

Dim dt As String

sht_nm = ActiveSheet.Name

dt = Format(Now(), " DD-MMM-YY")


'Save PDF Report to Folder

.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

"C:\Users\Peter\Desktop\Finance\PDF Reports\" & sht_nm & dt & ".pdf", Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _

False


I have this code to print each sheet to the right of “Reports” which I have tried to marry to the above code but cannot get it to work. Please help



PrintSheets()

Dim idx, i As Long

idx = Sheets("Reports").Index + 1

For i = idx To Sheets.Count

Sheets(i).PrintOut

Next

 

[John_w]

Try this macro:

Public Sub Save_Sheets_As_PDF()

    Dim PDFfile As String
    Dim currentSheet As Worksheet
    Dim i As Long
    Dim replaceSelected As Boolean
        
    PDFfile = "C:\Users\Peter\Desktop\Finance\PDF Reports\" & ActiveSheet.Name & Format(Now, " DD-MMM-YY") & ".pdf"
    
    With ActiveWorkbook
        Set currentSheet = .ActiveSheet
        replaceSelected = True
        For i = .Sheets("Reports").Index + 1 To .Sheets.Count
            .Sheets(i).Select replaceSelected
            replaceSelected = False
        Next
        .ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
            Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        currentSheet.Select True
        MsgBox "Created " & PDFfile
    End With
    
End Sub

 

[Peter Muller]

Try this macro:

Public Sub Save_Sheets_As_PDF()

    Dim PDFfile As String
    Dim currentSheet As Worksheet
    Dim i As Long
    Dim replaceSelected As Boolean
       
    PDFfile = "C:\Users\Peter\Desktop\Finance\PDF Reports\" & ActiveSheet.Name & Format(Now, " DD-MMM-YY") & ".pdf"
   
    With ActiveWorkbook
        Set currentSheet = .ActiveSheet
        replaceSelected = True
        For i = .Sheets("Reports").Index + 1 To .Sheets.Count
            .Sheets(i).Select replaceSelected
            replaceSelected = False
        Next
        .ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
            Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        currentSheet.Select True
        MsgBox "Created " & PDFfile
    End With
   
End Sub

only the active page prints, and not all the pages to the right of "Reports"

 

[John_w]

It works perfectly for me. By 'pages' do you mean sheets (tabs)?

What is displayed by this line, inserted above the For i = line?

        MsgBox .Sheets("Reports").Index + 1 & " " & .Sheets.Count

 

[Peter Muller]

It works perfectly for me. By 'pages' do you mean sheets (tabs)?

What is displayed by this line, inserted above the For i = line?

        MsgBox .Sheets("Reports").Index + 1 & " " & .Sheets.Count

By 'pages' do you mean sheets (tabs)? - Yes, I do

line above "for i" is as you supplied:

With ActiveWorkbook
Set currentSheet = .ActiveSheet
replaceSelected = True
For i = .Sheets("Reports").Index + 1 To .Sheets.Count
.Sheets(i).Select replaceSelected
replaceSelected = False
Next


Question - what is this code that is included in your reply? It is not in the original code you gave me.

MsgBox .Sheets("Reports").Index + 1 & " " & .Sheets.Count

 

[John_w]

I want you to insert this new line:

MsgBox .Sheets("Reports").Index + 1 & " " & .Sheets.Count

immediately above this existing line:

For i = .Sheets("Reports").Index + 1 To .Sheets.Count

and tell me what it displays.

 

[Peter Muller]

following msg box displayed:

All the tabs that are saved to the PDF file is combined into one file instead of each tab to it's own/separate PDF file.

My mistake for not clearly specifying that each sheet being saved as a PDF file must be separate. The PDF files are sent to different recipients, therefore it cannot be combined into 1PDF

 

[John_w]

No worries.

Public Sub Save_Each_Sheet_As_PDF()

    Dim folder As String
    Dim currentSheet As Worksheet
    Dim i As Long
        
    folder = "C:\Users\Peter\Desktop\Finance\PDF Reports\"
    
    With ActiveWorkbook
        Set currentSheet = .ActiveSheet
        For i = .Sheets("Reports").Index + 1 To .Sheets.Count
            .Sheets(i).ExportAsFixedFormat Type:=xlTypePDF, Filename:=folder & .Sheets(i).Name & Format(Now, " DD-MMM-YY") & ".pdf", _
                Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Next
        currentSheet.Select
    End With
    MsgBox "Done"
    
End Sub

 

[Peter Muller]

No worries.

Public Sub Save_Each_Sheet_As_PDF()

    Dim folder As String
    Dim currentSheet As Worksheet
    Dim i As Long
       
    folder = "C:\Users\Peter\Desktop\Finance\PDF Reports\"
   
    With ActiveWorkbook
        Set currentSheet = .ActiveSheet
        For i = .Sheets("Reports").Index + 1 To .Sheets.Count
            .Sheets(i).ExportAsFixedFormat Type:=xlTypePDF, Filename:=folder & .Sheets(i).Name & Format(Now, " DD-MMM-YY") & ".pdf", _
                Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Next
        currentSheet.Select
    End With
    MsgBox "Done"
   
End Sub

Works perfectly, thank you so much

 

[Peter Muller]

Works perfectly, thank you so much

Hi John,

If I only want certain columns to display on the saved PDF doc, e.g., columns A-I only, where the source sheet extends to column AE, where in the code you gave me will I insert this instruction?

Example:

Columns("J:AO").EntireColumn.Hidden = True

And

Columns("A:A").ColumnWidth = 1.43

Then after saving the sheet, to unhide those columns again?