Print to PDF, VBA question

smdppl

New Member
Joined
Apr 7, 2015
Messages
7
Good day everyone,

I have a unique VBA issue (or so I think I do). I have a workbook that will print multiple worksheets within it to a single PDF file. However, not every file needs to be printed, and to determine if the page should printed is based on the value of one cell. Here's what my code looks like:

VBA Code:
Sub PrintPDF2()
' PrintPDF2 Macro
' Keyboard Shortcut: Ctrl+p
' Make sure Indv Rep is on the lab number you want to start with or the code won't generate the PDF files necessary

Dim num As Integer
Dim x As Integer

For num = 1 To 132
    Sheets("Indv Rep").Select
    Range("A1:k63").Select
    ' The first line indicates which labs you are printing. Make sure you have cell A1 selected in your worksheet and the 
    'range above always begins at A1 and ends where the data on the INDV REP page stops
    
    x = num
    Selection.Replace what:="$" & num, Replacement:="$" & num + 1, LookAt:=xlPart, _
            Searchorder:=xlByRows, MatchCase:=False
    
    If Range("f7").Value <> "0" Then
            
   Sheets(Array("indv rep", "chart1", "chart2")).Select
   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "X:\YY\Proficiency Sample Program\ZZ\XXYY\Final\Lab " & Worksheets("Indv Rep").Range("A1").Value & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

End If
    
Next num

Selection.Replace what:="$" & x + 1, Replacement:="$2", LookAt:=xlPart, _
        Searchorder:=xlByRows, MatchCase:=False
  

End Sub

What this code currently does, is it will print each lab's information to a PDF file for me which I send out to the labs for review. However, the code prints all 132 labs (including the blanks) rather than just the labs with information. I want to skip the empty files and print only the ones containing information. But I run into an issue when printing to PDF, it prints every lab even if the lab's file is empty. I just want to skip the empty files and print the ones containing information. Any help is greatly appreciated! Thank you!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Thank you for your reply, MrTomasz. Unfortunately, it didn't solve the issue, it is still printing the blank files.
 
Upvote 0
F7 can't be blank, or I could use another cell if that would be better. F7 should contain a value from an active lab, if the cell is empty it means there is no data from a specific laboratory.
 
Upvote 0
maybe try

VBA Code:
if Sheets("Indv Rep").Range("f7").Value <> "" Then
 
Upvote 0
maybe try

VBA Code:
if Sheets("Indv Rep").Range("f7").Value <> "" Then
Sorry, this one didn't work out either. I sincerely thank you for trying to help, I'll live with deleting the extra files, not that big of a deal, more of a vanity project. Thanks again.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top