asked on
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"
Range("A6:K42").Select
End If
If Sheets(" Page1MilesLog").Range("B15
Range("A6:K42").Select
End If
If Sheets(" Page2MilesLog").Range("B15
Range("A6:K42").Select
End If
If Sheets(" Page3MilesLog").Range("B15
Range("A6:K42").Select
End If
If Sheets(" Page4MilesLog").Range("B15
Range("A6:K42").Select
End If
If Sheets(" Page5MilesLog").Range("B15
Range("A6:K42").Select
End If
If Sheets(" Page6MilesLog").Range("B15
Range("A6:K42").Select
End If
Selection.ExportAsFixedFor
Quality:=xlQualityStandard
IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub
Sample-1.pdf Sample 1 Correct outcome of current code
Sample-2.pdf Sample 2 Incorrect print version
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"
Range("A6:K42").Select
End If
If Sheets(" Page1MilesLog").Range("B15
Range("A6:K42").Select
End If
If Sheets(" Page2MilesLog").Range("B15
Range("A6:K42").Select
End If
If Sheets(" Page3MilesLog").Range("B15
Range("A6:K42").Select
End If
If Sheets(" Page4MilesLog").Range("B15
Range("A6:K42").Select
End If
If Sheets(" Page5MilesLog").Range("B15
Range("A6:K42").Select
End If
If Sheets(" Page6MilesLog").Range("B15
Range("A6:K42").Select
End If
Selection.ExportAsFixedFor
Quality:=xlQualityStandard
IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub
Sample-1.pdf Sample 1 Correct outcome of current code
Sample-2.pdf Sample 2 Incorrect print version
Printers and ScannersVBAMicrosoft OfficeMicrosoft Excel
Last Comment
Kim Dygert
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
Thanks again and Happy Holidays to you both!
Kim
Open in new window
See https://www.techonthenet.com/excel/formulas/isempty.phpAlso, I notice you have a blank in front of the sheet name. Does the code work if you remove that?