Print multiple sheets as PDF

[sfran]

Hello, i'm attempting to print specific sheets as a single .pdf document. I have referenced several other threads, but I still seem to be missing something. The code I have does begin to publish the documents, but then errors out. I have confirmed that both the filename and path are populated correctly.

Sub printer()

Dim FName As String
Dim FPath As String

FName = Sheets("Project Description").Range("L3")

FPath = "C:\" & FName & ".pdf"

Sheets("Project Description").Activate
ThisWorkbook.Sheets(Array("Test 1", "Test 2")).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
FPath, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub


Thank you for your help.

 

[John_w]

What's the error and on which line?

 

[sfran]

Hello... The error is a Run-Time error (see pic below) on the ActiveSheet.ExportAsFixedFormat.... command

Thanks!

 

[John_w]

Is the PDF open? You have to close the PDF before running the macro again.

 

[Kenneth Hobson]

You are not allowed to put files in the root folder of drive C:. Put files in subfolders.

When building filenames, they must be legal names. Dates are typical examples. Use Format() to format a date to a legal string for a filename.

 

[sfran]

Thank you Kenneth! .. I was saving directly to the root folder. Once changed to a subfolder, its working as expected.

Thanks!

 

[sfran]

I just tried adding more sheets to the array ... I have a total of 7 sheets that I need to print. (In the code I sent you, it was a test showing only 2). It seems if I add more than 2 sheets to the array, I get a " Run-time error '9'" Subscript out of range " .

Is there a way to include more than 2 sheets (i need 7) to the array that I can print?

Thanks,
Sean

 

[Kenneth Hobson]

The number in the array does not matter.

The worksheet names that you put in the array, must exist or it will error. These are worksheet names from the tabs, and not the codename.

Your activate is not needed.

Be aware that no matter what order you put into the array, the worksheet index order governs. For example, if you want sheet4 to be page one and sheet2 to be page two, then sheet4 must have an index order less than sheet2. When I did not want to reorder my worksheets in my workbook, I just copied the worksheets to a scratch/blank workbook with worksheets in order the way that I wanted.

Sub printer()
  Dim FName As String, FPath As String
  
  'FName = Sheets("Project Description").Range("L3")
  'FPath = "C:\" & FName & ".pdf"
  FPath = "d:\t\test.pdf"
  
  'Sheets("Project Description").Activate 'not needed
  ThisWorkbook.Sheets(Array("Sheet4", "Sheet2")).Select
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
  FPath, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
  IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

 

[John_w]

You are not allowed to put files in the root folder of drive C:. Put files in subfolders.

Thanks Ken, I didn't know that.