Exporting multiple sheets to .pdf via VBA code

Question

I have seen this question but it doesn't quite answer my question - excel vba not exporting pagesetup to pdf correctly

I have the same problem of the specified ranges in each sheet not being exported when utilising code to create the .pdf output. Everything on each sheet is exported so each worksheet is spread across two or more pages. The print ranges for each sheet are set up to print the specified area onto one sheet.

I have tried to adapt the code in the link but it doesn't seem to work with multiple sheets.

The code I am attempting to use in its unadapted form

Sub ClientPDFOutput()

If Sheets("File Data").Range("FD_FileName") = "" Then
'   MsgBox ("Save the file before exporting to a .pdf fomrat"), vbInformation, "Save File"

'   Exit Sub
   Else
End If

ActiveSheet.Unprotect Password:=strPassword

Range("UI_Status") = "Creating client PDF output - Please wait"

SelectSheets

Application.ScreenUpdating = False

Sheets(arrSheets).Select

strFilename = "Test"

Selection.ExportAsFixedFormat _
   Type:=xlTypePDF, _
   filename:=ActiveWorkbook.Path & "\" & strFilename & ".pdf", _
   Quality:=xlQualityStandard, _
   IncludeDocProperties:=True, _
   IgnorePrintAreas:=True, _
   OpenAfterPublish:=False

Sheets("User Input").Select

Range("UI_Status") = "Client .pdf output created and saved"

ActiveSheet.Protect Password:=strPassword

Application.ScreenUpdating = True

MsgBox ("The client output in .pdf format has been created and saved"), vbInformation, ".pdf Created"

End Sub

AND

Sub SelectSheets()

Dim rngSheets As Range

Set rngSheets = Sheets("File Data").Range("D_OutputSheets")

If rngSheets.Count = 1 Then
   arrSheets = rngSheets.Value2
   Else
   arrSheets = Application.Transpose(rngSheets.Value2)
End If

End Sub

After a bit more experimenting I established that my print ranges on each of the pages was off so corrected these.

I added code to select the print range of each sheet before all being selected as part of the sheet array, but the print range in the first sheet of the array is being duplicated across all sheets. So if the range in sheet 1 is B4:P61 and sheet 2 print range is B4:M48, sheet 2 is having B4:P61 selected when the array of sheets is selected.

This prints out the selected ranges which is correct for sheet 1 but wrong for the rest of the sheets.

When I do this manually by selecting all the sheets, File, Export then all the sheets print ranges are exported so why when this is recorded and put into a routine it is being ignored?

Answer 1

Please try to change IgnorePrintAreas property.

Selection.ExportAsFixedFormat _
   Type:=xlTypePDF, _
   filename:=ActiveWorkbook.Path & "\" & strFilename & ".pdf", _
   Quality:=xlQualityStandard, _
   IncludeDocProperties:=True, _
   IgnorePrintAreas:=False, _
   OpenAfterPublish:=False

Answer 2

I would advise using the following to ensure your page setup fixes it to a single page:

With ActiveSheet.PageSetup
 .FitToPagesWide = 1
 .FitToPagesTall = 1
End With

Delete either the first or second line in the with statement to suit your preferences, or keep both.

Further I see you have selection.ExportAsFixedFormat. Ensure that your selected area is correct or use fixed ranges. You may also want to dynamically determine your furthest row/column and add that to the variable PrintRange. Getting the actual usedrange. Set IgnorePrintAreas:=False

Dim PrintRange As Range

Set PrintRange = Range("A1:XX100")

PrintRange.ExportAsFixedFormat Type:=xlTypePDF, _
                                Filename:=Filename, _
                                Quality:=xlQualityStandard, _
                                IncludeDocProperties:=True, _
                                IgnorePrintAreas:=False, _
                                OpenAfterPublish:=False

Answer 3

Try to add a "For each sheet in activeworkbook" (it worked for me):

Sub Extract_pdfs()

Dim wb As Workbook
Dim sh As Worksheet

Set wb = ThisWorkbook

For Each sh In wb.Worksheets

    sh.Select

    pdf_name = sh.Name & ".pdf"

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=ActiveWorkbook.Path & pdf_name, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

Next

End Sub

Answer 4

If you are trying to print multiple ranges across multiple sheets, into one pdf, you can try using a union function to combine them, I however have not had any luck with using unions so I have done a somewhat longer way to do that. Essentially I create a new page and copy the ranges over to it (in the format I want it) I make sure to delete the page afterwards as it makes running the macro again much easier.

t= 1
ThisWorkbook.Sheets.Add.Name = "Print"

set rowcount = range(tocopy).row.count

Range(tocopy).SpecialCells(xlCellTypeVisible).Copy
With Sheets("Print").Cells(t, 1)
.PasteSpecial xlPasteColumnWidths
.PasteSpecial xlPasteFormats
.PasteSpecial xlPasteValuesAndNumberFormats
End With
t = t + rowcount

'keep doing for all ranges

Sheets("Print").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"Name.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

and then I continue to do that until I have all the ranges on the page and I export the page as a pdf. then delete the sheet "Print" afterwards.

However if you are just looking to print each on a separate page you can try

dim printing() as string

printing(1) = "Range1"
printing(2) = "Range2"

for each section in printing
Range(section).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"Name.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
next section

As I was mentioning above, you could try to set a union function for it (if you have better luck than I do)

dim printing as range 

printing = union(range1,range2,range3)

printing.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"Name.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

Good luck! hope this helps, also note code may not be exactly right as it is untested!