0

So in an example code like below, it works in PDFing specific sheets in an excel by specifying the sheet #, but is there a way to specify PDF range by Sheet name instead of number?

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\xxxx\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
braX
  • 11,506
  • 5
  • 20
  • 33
John Davis
  • 43
  • 8
  • Does this answer your question? [Save multiple sheets to .pdf](https://stackoverflow.com/questions/14404650/save-multiple-sheets-to-pdf) – BigBen Jan 15 '20 at 14:52
  • Hi, thank you for the input, but not quite, because that is specifying the sheet by name for selection, not specifying sheet by name as two ends of a range. – John Davis Jan 15 '20 at 15:01
  • Ok I misunderstood your question then. – BigBen Jan 15 '20 at 16:03
  • Does each worksheet have a different range you want to print to .pdf? Are there worksheet that you don't want to print? Are you trying to print only one specific worksheet in the workbook? – GMalc Jan 15 '20 at 16:55
  • Yes, so that number of pages to pdf is dynamic based on where the PDF begin and end tabs are. – John Davis Jan 15 '20 at 17:03
  • Is that yes to all three questions? – GMalc Jan 15 '20 at 17:18
  • Please update your question with a specific explanation and example of what you are trying to accomplish. – GMalc Jan 15 '20 at 17:23

1 Answers1

2

If I understand right you want to start off this sub by changing this:

startSheet = 1
endSheet = 2

To sheet names instead of their index number. This can be easily done by adding the sheets as a reference, and having the code take the index from them, by replacing the above with this:

startSheet = Sheets("Start sheet name here").Index
endSheet = Sheets("End sheet name here").Index

The rest of the code will run just like before.

Please note you instead of hard-coding this, you can also do this with a simple input box instead:

startSheet = Sheets(InputBox("Sheet name?", "CreatePDF")).index

This could make it more accessible and user friendly rather than having to mess with the VBA code all the time.

Plutian
  • 2,276
  • 3
  • 14
  • 23
  • Hi thank you for the info, but after replacing it with startSheet = Sheets("Start sheet name here").Index, endSheet = Sheets("End sheet name here").Index, I am getting Run-time error '9': Subsecript out of range error. – John Davis Jan 15 '20 at 16:19
  • Did you replace the text "Start sheet name here" with the actual name of the sheet you want to start from? (note: this needs to be in "quotes") please make sure this is spelled correctly as well. – Plutian Jan 15 '20 at 16:34
  • Yes put them on quotes – John Davis Jan 15 '20 at 16:35