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

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
Kim DygertAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hdhondtCommented:
Try using the ISEMPTY function instead of checking for an empty string, i.e.:

If IsEmpty(Sheets(" Page1MilesLog").Range("B15")) = True Then ...

Open in new window

See  https://www.techonthenet.com/excel/formulas/isempty.php

Also, I notice you have a blank in front of the sheet name. Does the code work if you remove that?
0
Ejgil HedegaardCommented:
Try this
Option Explicit

Sub PrintPacket()
    Dim arSheetsToPrint() As String, i As Integer
    
    ReDim arSheetsToPrint(1)
    arSheetsToPrint(0) = "APCF"
    arSheetsToPrint(1) = " Page1MilesLog"
    
    For i = 2 To 6
        If IsEmpty(Sheets(" Page" & i & "MilesLog").Range("B15")) = False Then
            ReDim Preserve arSheetsToPrint(UBound(arSheetsToPrint) + 1)
            arSheetsToPrint(UBound(arSheetsToPrint)) = " Page" & i & "MilesLog"
        End If
    Next i
    
    Sheets(arSheetsToPrint).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=True
    
    Sheets("APCF").Select
End Sub

Open in new window


Change code if the sheet names does not have a space in front.
Use replace Ctrl+H and replace " Page" with "Page"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kim DygertAuthor Commented:
I recorded a macro to create the Array, not sure why the space is in front of the tab names, but it is.  The ISEMPTY by itself did not work, but the code from Ejgil Hedgegard did the trick, thank you both so much!  I'm miffed at how little code did all that.  I will study this to fully understand, but until that time, I can roll this out to folks to save them (and me) many hours!!!

Thanks again and Happy Holidays to you both!
Kim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Printers and Scanners

From novice to tech pro — start learning today.