Printing multiple pages into PDF

Question

I am trying to print multiple sheets in PDF Format. Below is the code but it is giving subscript out of range error. Please help

  PdfFile = ActiveWorkbook.FullName
  Worksheets("Report").PageSetup.Orientation = xlLandscape
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = "SPC Report " & Format(Now, "dd-mmm-yy h-mm-ss") & ".pdf" 'PdfFile & "_" & Worksheets("Report").name & ".pdf"

  ' Export activesheet as PDF
  With Worksheets("Report")
    ThisWorkbook.Sheets(ShtNames).Select
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:="SPC Report " & Format(Now, "dd-mmm-yy h-mm-ss") & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    ThisWorkbook.Sheets("Report").Select
  End With

Here is where I declare ShtNames and assign values to it.

 Dim ShtNames() As String
  ReDim ShtNames(1 To ActiveWorkbook.Sheets.Count / 2 + 1)
  i = Sheets.Count
  j = 1
  Do While i > Sheets.Count / 2
    ShtNames(j) = Sheets(i).Name
    i = i - 1
    j = j + 1
  Loop

I have edited the question for your reply. Also, the individual array variables in ShtNames is proper such that ShtNames(1), ShtNames(2) are all working properly but it is unable to select the given sheets in a single select statement. — Lionel Zed, Aug 14 '17 at 10:28
https://stackoverflow.com/questions/14404650/save-multiple-sheets-to-pdf — Luuklag, Aug 14 '17 at 10:35
Possible duplicate of [Save multiple sheets to .pdf](https://stackoverflow.com/questions/14404650/save-multiple-sheets-to-pdf) — Wolfie, Aug 14 '17 at 10:37

Prebsus · Answer 1 · 2017-08-14T13:15:34.823

Initial problem from OP seems to have been solved. Updated answer to question on how to print sheets in reverse is now included at bottom of answer Try setting j = 0 in the code where your declare ShtNamesand assign values to it:

I don't see any index specification at the top of your code, so I assume that your arrays are all 0-indexed.

Your Do While loop uses the counter j to access elements in your ShtNames-array, however, it seems to me that you start by accessing the second element of that array (As ShtNames(0)is the first element, and ShtNames(1) is the second element).

I would suspect that when you then try to assign the last sheet-name to the last index of your ShtNames-array, you're index 1 larger than the size you ReDim'd it initially.

To print sheets in reverse order, assign your ShtNames in reverse order:

Dim ShtNames() As String
  ReDim ShtNames(1 To ActiveWorkbook.Sheets.Count / 2 + 1)
  i = Sheets.Count
  j = UBound(ShtNames)
  Do While i > Sheets.Count / 2
    ShtNames(j) = Sheets(i).Name
    i = i - 1
    j = j - 1
  Loop

Yes. I got it and solved it. But I have one problem. I want to print the pages as per the order in the ShtNames array (i.e) the pdf should be from reverse. The last page should come first and the first page last. Help here is appreciated. — Lionel Zed, Aug 14 '17 at 11:38
I would recommend assigning your `ShtNames` in reverse order, i.e.: `j = UBound(ShtNames)` Then use `j = j-1`in your `Do While` loop. Does that solve your problem? — Prebsus, Aug 14 '17 at 11:56

Printing multiple pages into PDF

1 Answers1