Sep 04 2019 06:36 AM
Is it possible to print multiple pages without stoping for each page? I have a print command that prints three different sheets in PDF, but stops at the beginning of each sheet and requires me to save that corresponding sheet as a different name. I would like it just to print one continuous report. This is my coding.
Sub Macro1()
'
' Macro1 Macro
'
'
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Sheets("Workup 1").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Sheets("PO List").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub
Sep 04 2019 08:09 AM
Hi,
If you just want to save each sheet of them as PDF, you can use this code instead:
Sub Macro1()
'In the below line type the full path of the location you want to save in
ChDir "C:\Users\<UserName>\Desktop"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ActiveSheet.Name, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
Sheets("Workup 1").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ActiveSheet.Name, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
Sheets("PO List").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ActiveSheet.Name, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
End Sub
If you want to run the command on all sheets in the workbook, you can encapsulate it in a For-Each-Loop as follows:
Sub Macro1()
ChDir "C:\Users\<UserName>\Desktop"
For Each Sheet In Sheets
Sheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
Sheet.Name, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
Next Sheet
End Sub
Hope that helps
Sep 04 2019 09:53 AM
Thank you for the response.
The first code you suggest would let me save them as 3 different documents (PDF)? If so, that's what my current code does. I am looking to have it print/save PDF as one continuous form as if it was all one page. That would be your second code? I am confused on how you explained you options.
Sep 04 2019 10:30 AM - edited Sep 04 2019 10:34 AM
The first code saves the specific sheets as PDF, each one in a separate PDF file which are the currently selected sheet, sheet Workup 1, and sheet PO List.
The second code does the same thing. but the difference is that it loops through each sheet in the workbook and save it as PDF regardless of the sheet names and count. It also names the PDF as the original sheet name in Excel.
None of them printout the PDFs, they just save as PDF locally!
Sep 04 2019 10:43 AM - edited Sep 04 2019 10:44 AM
If you want to save all worksheets in the workbook in one continuous PDF, try this code instead:
Sub Macro2()
ChDir "C:\Users\<UserName>\Desktop"
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Name, Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End Sub
Regards
Sep 05 2019 10:31 AM
yes... I would like all three sheets (that my current code saves) to be saved as one PDF, NOT the entire work book. Your code would save the entire workbook.... correct?
Sep 05 2019 10:17 PM
Sep 05 2019 11:05 PM
Hi,
I figured out a good solution using this code:
Sub Macro5()
'Save specific sheets as PDF, but all in one file.
ChDir "C:\Users\Haytham\OneDrive\Desktop"
Sheets(Array("Workup 1", "PO List", "Sheet2")).Copy
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Name, Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
ActiveWorkbook.Close SaveChanges:=False
End Sub
This is the explanation of the commands in the code:
Before you run the code, make sure that:
Hope that helps
Sep 06 2019 06:58 AM
Not sure what is going on. I cannot get a file path to work. I have tried a number of different ways. Here is my last attempt. Keep giving me a "Run-time error '76': Path not found"
Sep 06 2019 07:21 AM
The error is expected because you didn't change this placeholder <UserName> in the path with the existing user name in your computer!
The best way to get the path of any file or folder is to press Shift and mouse Right-click simultaneously.
This will add a new option in the right-click menu called (Copy as path).
Also, you can replace the path line with the below which will save the PDF in the same path that the original workbook was saved in!
ChDir ThisWorkbook.Path
Sub Macro5()
'Save specific sheets as PDF, but all in one file.
ChDir ThisWorkbook.Path
Sheets(Array("Sheet1", "PO List")).Copy
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Name, Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
ActiveWorkbook.Close SaveChanges:=False
End Sub
Regards