I am very new to VBA and trying to create a macro that will export a PDF of only the print areas that have been populated with values from another sheet in the workbook.
I have a sheet with 8 A4 forms marked out with 8 separate print areas. Depending on the project we are working on, the number of forms filled and needed to be PDF’d varies. I would like a simple macro that can detect which forms are blank, and not include them in the PDF.
I was wondering if it is possible by using VBA to detect if a cell is blank or has any value and if it does contain a value, include it in the PDF? On the first form, cell R5 is an example of a cell that if it has any value, needs to be in the PDF.
Currently I’m using this code to PDF and rename. It is cobbled together from about three different tutorials and i’m sure looks very unwieldy.
Sub GenerateSDS() Sheets("SDS").Select Dim wsA As Worksheet Dim wbA As Workbook Dim strName As String Dim strPath As String Dim strFile As String Dim strPathFile As String Dim myFile As Variant Dim lOver As Long On Error GoTo errHandler Set wbA = ActiveWorkbook Set wsA = ActiveSheet strPath = wbA.Path If strPath = "" Then strPath = Application.DefaultFilePath End If strPath = strPath & "" strName = wsA.Range("A92").Value _ & " _ " & "SDS" & " _ " & "BLANK" strFile = strName & ".pdf" strPathFile = strPath & strFile If bFileExists(strPathFile) Then lOver = MsgBox("Overwrite existing file?", _ vbQuestion vbYesNo, "File Exists") If lOver <> vbYes Then myFile = Application.GetSaveAsFilename _ (InitialFileName:=strPathFile, _ FileFilter:="PDF Files (*.pdf), *.pdf", _ Title:="Select Folder and FileName to save") If myFile <> "False" Then strPathFile = myFile Else GoTo exitHandler End If End If End If wsA.ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:=strPathFile, _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=False Sheets("Enter Data Here").Select MsgBox "PDF file has been created: " _ & vbCrLf _ & strPathFile exitHandler: Exit Sub errHandler: MsgBox "Could not create PDF file" Resume exitHandler End Sub '============================= Function bFileExists(rsFullPath As String) As Boolean bFileExists = CBool(Len(Dir$(rsFullPath)) > 0) End Function '=============================