is there a way to PDF range of worksheets in Excel between two specific tabs using VBA?

Question

Trying to figure out if this is doable, I know how to PDF specific excel worksheets through VBA, but is it possible to PDF a range of worksheets by specifying start and end worksheets? So if there is 10 worksheets in a workbook, Sheet1 through Sheet10, can I PDF worksheets between Sheet1 and Sheet10 without specifying every worksheet? Thank you.

Answer 1

If you're trying to get one PDF out of specific sheets, it might make sense in your code to create a new workbook, then copy the specific sheets you need to that new workbook, then use the Workbook.ExportAsFixedFormat Method to create the PDF.

In my personal experience manually attempting to do what you are describing, Excel really wants those sheets in consecutive order, and the documentation around workbook.exportasfixedformat doesn't provide enough detail about selecting a range of sheets for your PDF. In fact, it's documented like you are using Word, where the To and From parameters are looking for page numbers to start and finish with. This is a very straightforward way of handling converting a section of a Word document into a PDF, but pages of an Excel workbook is much more ambiguous.

Answer 2

You might want to try to do something like this. This will loop and create all sheets into one pdf. This will also write them out to a directory to your desktop.

Sub createPdf()

Dim SheetArr() As String
Dim i As Integer
Dim startSheet As Integer
Dim endSheet As Integer

startSheet = 1
endSheet = 2
Dim folderPath As String
folderPath = "C:\Users\JMA337\Desktop\Pdfs" 'change to your user.
MkDir (folderPath)

For Each ws In ThisWorkbook.Worksheets 'This statement starts the loop
    If ws.Index >= startSheet And ws.Index <= endSheet Then ' <> "Sheet3" Then
        ReDim Preserve SheetArr(i)
        SheetArr(i) = ws.Name

        i = i + 1
        Debug.Print (ws.Name)

    End If
Next

Sheets(SheetArr).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=folderPath & "\Sales", _
openafterpublish:=False, ignoreprintareas:=False

MsgBox "All done with pdf's"

End Sub

Answer 3

Sounds like you need to create a loop.

If you just want to PDF every sheet in the work, use a For Each loop (for each sht in workbook.worksheets)

If you want to PDF only a certain range of of sheets, like only sheets 1 through 10, even if the workbook has 20 worksheets, create a loop with a variable (e.g. intX). The range of the would be the sheet numbers to PDF. Within the loop, call each sheet as 'Sheets("Sheet" & intX) to PDF it.

If you want to print the sheets as one PDF, use the loop to loop through as select the desired sheets, setting the replace property to false

For intX = 1 to 5
    Sheets("Sheet" & intX).Select False
Next intX

Then you can treat the selected sheets as one entity when you exportasfixed format.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFolderPath & "\" & strFileName, Quality:=xlQualityMinimum, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False