troubleshooting Question

VBA to print only populated sheets to PDF, Open after publish all in one document.

Avatar of Kim Dygert
Kim Dygert asked on
Printers and ScannersVBAMicrosoft OfficeMicrosoft Excel
3 Comments1 Solution175 ViewsLast Modified:
I work in a school district where I have a "portfolio" of forms our teachers use on a regular basis.  I am trying to make this as automated for them as possible.  With that in mind, I wrote the following code after many many days and lots of frustration, but the code finally runs.  My problem is, the result is still printing all pages where cell B15 is NOT populated.  So two issues:

1. I ALWAYS want sheets "APCF" and "Page1MilesLog"  to print and ONLY additional sheets to print if cell B15 has any input by the user.  The current code still prints all pages.  What modification to the code do I need to make this work?

2. The print to PDF comes up with my document settings (page 1 is portrait orientation, the remaining pages are landscape, see sample 1 attached) but when I go to print to a physical printer, it prints out all portrait, see sample 2 attached.  Is there VBA that can fix this?

Your help is appreciated!
Kim

Sub PrintPacket()

Sheets(Array("APCF", " Page1MilesLog", " Page2MilesLog", " Page3MilesLog", _
    " Page4MilesLog", " Page5MilesLog", " Page6MilesLog")).Select
    Sheets("APCF").Activate

    If Sheets("APCF").Range("C14") <> "" Then
    Range("A6:K42").Select
    End If
    If Sheets(" Page1MilesLog").Range("B15") <> "" Then
    Range("A6:K42").Select
    End If
    If Sheets(" Page2MilesLog").Range("B15") <> "" Then
    Range("A6:K42").Select
    End If
    If Sheets(" Page3MilesLog").Range("B15") <> "" Then
    Range("A6:K42").Select
    End If
    If Sheets(" Page4MilesLog").Range("B15") <> "" Then
    Range("A6:K42").Select
    End If
    If Sheets(" Page5MilesLog").Range("B15") <> "" Then
    Range("A6:K42").Select
    End If
    If Sheets(" Page6MilesLog").Range("B15") <> "" Then
    Range("A6:K42").Select
    End If
   
     Selection.ExportAsFixedFormat Type:=xlTypePDF, _
     Quality:=xlQualityStandard, IncludeDocProperties:=True, _
     IgnorePrintAreas:=False, OpenAfterPublish:=True
     
     
End Sub
Sample-1.pdf Sample 1 Correct outcome of current code
Sample-2.pdf Sample 2 Incorrect print version
ASKER CERTIFIED SOLUTION
Ejgil Hedegaard

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros