Continuous printing with Macros possible?

Brass Contributor

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

9 Replies

@Budman36

 

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

@Haytham Amairah 

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.

 

@Budman36

 

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!

@Budman36

 

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

@Haytham Amairah 

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?

@Budman36

 

Yes, the code would save the entire workbook!

@Budman36

 

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:

  • 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

@Haytham Amairah 

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"

SnipImage.JPG

@Budman36

 

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