Excel save as PDF only if matches criteria

Jyotirmaya

Board Regular
Joined
Dec 2, 2015
Messages
204
Office Version
  1. 2019
Platform
  1. Windows
VBA Code:
'This method Saves each of the worksheets of current
'Excel file as individual PDF File using Excel VBA
Sub SaveEachSheetAsPDFFileED()
                            
Dim wsA     As Worksheet
Dim wbA     As Workbook
Dim WS_Count  As Long
Dim strFile, myfile As String

Set wbA = ActiveWorkbook
WS_Count = wbA.Worksheets.Count

For Each wsA In wbA.Sheets

        wsA.Activate
        
        'create default name for savng file
        strFile = "C:\Users\jyotirmaya\Desktop\Macro\" & wsA.Name & " " & Format(Date, "dd-mm-yyyy") & ".pdf"
        myfile = strFile

        'export to PDF if a folder was selected
      If wsA.Name = "COUNT" Or wsA.Name = "RAW DATA" Then

     'no code here
Else
             ActiveSheet.ExportAsFixedFormat _
                        Type:=xlTypePDF, _
                        Filename:=myfile, _
                        Quality:=xlQualityStandard, _
                        IncludeDocProperties:=True, _
                        IgnorePrintAreas:=False, _
                        OpenAfterPublish:=False
End If


     Next
End Sub

I am using this code to print as PDF all sheets of the excel except COUNT& RAW DATA sheet. In count sheet in Column A I have sheet names and in column B i have the information of number of rows data available in the sheet. I want that ifthe value of column B is more than 0 then only that sheet will be saved as PDF. Presently all the sheets are saving as pdf even blank sheets

In COUNT sheet Column B, I am using formula =COUNTIF(INDIRECT("'"&A1&"'!E:E"),"<>")-1 to count the number of rows data. I want that if this formula results more than zero then only the PDF will be made.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
VBA Code:
'This method Saves each of the worksheets of current
'Excel file as individual PDF File using Excel VBA
Sub SaveEachSheetAsPDFFileED()
                           
Dim wsA     As Worksheet
Dim wbA     As Workbook
Dim WS_Count  As Long
Dim strFile, myfile As String

Set wbA = ActiveWorkbook
WS_Count = wbA.Worksheets.Count

For Each wsA In wbA.Sheets

        wsA.Activate
       
        'create default name for savng file
        strFile = "C:\Users\jyotirmaya\Desktop\Macro\" & wsA.Name & " " & Format(Date, "dd-mm-yyyy") & ".pdf"
        myfile = strFile

        'export to PDF if a folder was selected
      If wsA.Name = "COUNT" Or wsA.Name = "RAW DATA" Then

     'no code here
Else
             ActiveSheet.ExportAsFixedFormat _
                        Type:=xlTypePDF, _
                        Filename:=myfile, _
                        Quality:=xlQualityStandard, _
                        IncludeDocProperties:=True, _
                        IgnorePrintAreas:=False, _
                        OpenAfterPublish:=False
End If


     Next
End Sub

I am using this code to print as PDF all sheets of the excel except COUNT& RAW DATA sheet. In count sheet in Column A I have sheet names and in column B i have the information of number of rows data available in the sheet. I want that ifthe value of column B is more than 0 then only that sheet will be saved as PDF. Presently all the sheets are saving as pdf even blank sheets

In COUNT sheet Column B, I am using formula =COUNTIF(INDIRECT("'"&A1&"'!E:E"),"<>")-1 to count the number of rows data. I want that if this formula results more than zero then only the PDF will be made.
Add a loop on the ELSE side to check for the .value >0 for each of your Col B totals, then only those will print inside the ELSE statement part.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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