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?
 
Ejgil HedegaardConnect With a Mentor Commented:
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.