Forum Discussion
Continuous printing with Macros possible?
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
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.
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 SubRegards
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?
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 SubThis is the explanation of the commands in the code:
- Point Excel to the location where the file will be saved
- Create a new temporary workbook consists only from the sheets that you specify
- Create a one PDF from all sheets in that workbook and save it in the Desktop
- Close the temporary workbook without saving
Before you run the code, make sure that:
- Replace this path "C:\Users\Haytham\OneDrive\Desktop" with your specific path
- Identify the sheets that you want to include in the Array function
- The name of the sheet in the Array function should be in double quotes and each name is separated from the other by a comma
Hope that helps
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!