Print multiple sheets as PDF

sfran

New Member
Joined
Mar 9, 2021
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

sfran

New Member
Joined
Mar 9, 2021
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
Hello... The error is a Run-Time error (see pic below) on the ActiveSheet.ExportAsFixedFormat.... command


1618258423090.png


Thanks!
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,752
Is the PDF open? You have to close the PDF before running the macro again.
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,173
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 
Solution

sfran

New Member
Joined
Mar 9, 2021
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
Thank you Kenneth! .. I was saving directly to the root folder. Once changed to a subfolder, its working as expected.

Thanks!
 

sfran

New Member
Joined
Mar 9, 2021
Messages
24
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Feb 6, 2007
Messages
3,173
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,791
Messages
5,638,336
Members
417,021
Latest member
moon miner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top