Excel save as PDF only if matches criteria

Jyotirmaya

Board Regular
Joined
Dec 2, 2015
Messages
192
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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Oddball2020

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
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.
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.
 

Forum statistics

Threads
1,136,444
Messages
5,675,894
Members
419,591
Latest member
mersanko

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
Top