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

 

[Peter Muller]

Good afternoon,

Hope I am posting this follow up question correctly seeing that i already received an answer to my original question.

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?

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

 

[mlcalves]

Example:

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

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

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

 

[Peter Muller]

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

Thank you, will check tomorrow morning

 

[John_w]

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?

Macro updated as requested. Also changed to loop through Worksheets instead of Sheets, because your columns request means we aren't dealing the Chart sheets.

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 = .Worksheets("Reports").Index + 1 To .Worksheets.Count
            .Worksheets(i).Columns("J:AO").EntireColumn.Hidden = True
            .Worksheets(i).Columns("A").ColumnWidth = 1.43
            .Worksheets(i).ExportAsFixedFormat Type:=xlTypePDF, Filename:=folder & .Worksheets(i).Name & Format(Now, " DD-MMM-YY") & ".pdf", _
                Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            .Worksheets(i).Columns("J:AO").EntireColumn.Hidden = False
        Next
        currentSheet.Select
    End With
    MsgBox "Done"
  
End Sub

More code is needed if you want the macro to restore the column widths.

 

[Peter Muller]

Macro updated as requested. Also changed to loop through Worksheets instead of Sheets, because your columns request means we aren't dealing the Chart sheets.

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 = .Worksheets("Reports").Index + 1 To .Worksheets.Count
            .Worksheets(i).Columns("J:AO").EntireColumn.Hidden = True
            .Worksheets(i).Columns("A").ColumnWidth = 1.43
            .Worksheets(i).ExportAsFixedFormat Type:=xlTypePDF, Filename:=folder & .Worksheets(i).Name & Format(Now, " DD-MMM-YY") & ".pdf", _
                Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            .Worksheets(i).Columns("J:AO").EntireColumn.Hidden = False
        Next
        currentSheet.Select
    End With
    MsgBox "Done"
 
End Sub

More code is needed if you want the macro to restore the column widths.

Thank you so much John, this is perfect ??