Save filled tables with filled cells only in PDF format

I am currently trying to modify a Visual Basic macro to only save the spreadsheets in a workbook that had filled cells in it.

The current macro just saves the entire 16-sheet workbook in PDF format, but at most 9 of those sheets are sometimes incomplete but still saved.

I would like a macro to automatically check if these sheets have been filled after clicking the SAVE button, and then continue to save the filled (complete) sheets as PDF.

I would hurt any help!

Below is the code how the macro now works, only saving the whole workbook. (An IF statement will be checked before it is saved to PDF.)

Sub SaveAsPDF()

    With ThisWorkbook.Sheets("COVERPage1PRINT")
        If (Len(.Range("C24")) = 0) Then
            MsgBox "Ensure Serial Number or Stamp number are filled."
            Exit Sub
        ElseIf (Len(.Range("H17")) = 0) Then
            MsgBox "Ensure Serial Number or Stamp Number are filled."
            Exit Sub

        Else
            ChDir _
            "P:\Cells\Spool & Sleeves Cell\Flow Plot Records\EFA\Saved EFA PDF Archive"
        fname = Sheets("COVERPage1PRINT").Range("H17")
        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "P:\Cells\Spool & Sleeves Cell\Flow Plot Records\EFA\Saved EFA PDF Archive\" & fname, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=True

        End If
    End With
End Sub

      

+3


source to share


2 answers


This should do the job (edited code)

Sub test1()

Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim test() As String
Dim i As Integer
Dim pdfpath As String
Dim sheets_to_be_checked() As Variant
Dim a As Boolean
pdfpath = ActiveWorkbook.Path 'YOU CAN ADD YOUR PDF SAVING LOCATION e.g. "C\Users\ABC\Desktop"

i = 0
sheets_to_be_checked = Array("Sheet1", "Sheet3")
Set wbBook = ActiveWorkbook

With ThisWorkbook.Sheets("COVERPage1PRINT")
    If (Len(.Range("C24")) = 0) Then
        MsgBox "Ensure Serial Number & Tag Number or Stamp number are filled."
        Exit Sub
    ElseIf (Len(.Range("H16")) = 0) Then
        MsgBox "Ensure Serial Number & Tag Number or Stamp Number are filled."
        Exit Sub
    ElseIf (Len(.Range("H19")) = 0) Then
        MsgBox "Ensure Serial Number & Tag Number or Stamp Number are filled."
        Exit Sub
    Else:
        For Each wsSheet In wbBook.Worksheets
            With wsSheet
                If IsInArray(wsSheet.Name, sheets_to_be_checked) Then
                    wsSheet.Activate
                    If WorksheetFunction.CountA(Range("D4:D9, E10:E15, F4:F9, G10:G15, H4:H9, I10:I15, J4:J9, K10:K15")) = 48 Then
                        ReDim Preserve test(i)
                        test(i) = wsSheet.Name
                        i = i + 1
                    End If
                Else:
                    ReDim Preserve test(i)
                    test(i) = wsSheet.Name
                    i = i + 1
                End If
            End With
        Next wsSheet
    End If
End With

ThisWorkbook.Sheets(test()).Select

ActiveSheet.ExportAsFixedFormat _
     Type:=xlTypePDF, _
     Filename:=pdfpath & "\ouput.pdf", _
     Quality:=xlQualityStandard, _
     IncludeDocProperties:=True, _
     IgnorePrintAreas:=False, _
     OpenAfterPublish:=True
End Sub


Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

      



The answer may vary slightly depending on your definition of completed worksheets. You will need to change the condition "If .UsedRange.Address <>" $ A $ 1 "Then" One of the possible alternatives above: WorksheetFunction.CountA (Range ("A1: Z100")) <> 0

Please let me know if you need help with a condition or code.

+1


source


It will depend on what you mean by "automatically checks if these sheets have been filled in". My crystal ball says each sheet has a header line and would be considered "populated" if there was any data below the first line. In this case, you can cycle through all the sheets and build an array of worksheet names to be selected. After selecting multiple worksheets, PDF creation will be on ActiveSheet.ExportAsFixedFormat

not ActiveWorkbook.ExportAsFixedFormat

and only those worksheets will be included in the PDF.

Dim w As Long, sWSs As String, vWSs As Variant
For w = 1 To Sheets.count
    With Sheets(w)
        If .Cells(1, 1).CurrentRegion.Rows.count > 1 Then _
            sWSs = sWSs & .Name & Chr(215)
    End With
Next w
If CBool(Len(sWSs)) Then
    vWSs = Split(Left(sWSs, Len(sWSs) - 1), Chr(215))
    Sheets(vWSs).Select
    ChDir _
        "P:\Cells\Spool & Sleeves Cell\Flow Plot Records\EFA\Saved EFA PDF Archive"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "P:\Cells\Spool & Sleeves Cell\Flow Plot Records\EFA\Saved EFA PDF Archive\" & fname, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
Else
    MsgBox "Nothing to publish to PDF."
End If

      



I tested this with my own workbook and then tried to incorporate your code specifics into my method. If it doesn't work please post a comment first and I can offer help.

0


source







All Articles