Script to determine number of pages to save/print "Microsoft Print to PDF"

[noveske]

I've tried everything at this point.
I have a template that goes up to 8 pages. The script will save all 8 pages everytime.

With AA2:AD99, the script is to determine the number of pages to save/print.
So every 6 should be 1 page. Up to 12 is 2. Anything more should be 3. I just stopped at 3 since it should never go higher.

No matter what I modify, it always just saves 8 pages. I could deleted 5 pages, but it would still be 3 everytime.

Is this just something that cannot be done or am I just overlooking something?

Values are entered into AA2:AD99. Then referenced to cells on pages to be "printed" saved.

Even tried different methods to set the ranges.
It could even be is there is a value and not formula in: D3, then print 1 page.
If there is a value in D40, then print 2 pages.
If there is a value in D76, then print 3 pages.

Sub SaveAsPDF()
    Dim ws As Worksheet
    Dim pdfName As String
    Dim printRange As Range
    Dim outputPath As String
    Dim usedRows As Long
   
    outputPath = ThisWorkbook.Path & "\Legal\"
   
    If Dir(outputPath, vbDirectory) = "" Then
        MkDir outputPath
    End If
   
    For Each ws In ThisWorkbook.Sheets(Array("F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P"))
        usedRows = Application.WorksheetFunction.CountA(ws.Range("AA2:AD99"))
       
        If usedRows > 0 Then
            pdfName = outputPath & ws.Name & ".pdf"
            Set printRange = ws.Range("A:W")
           
            Dim numPages As Long
            If usedRows <= 6 Then
                numPages = 1
            ElseIf usedRows <= 12 Then
                numPages = 2
            Else
                numPages = 3
            End If

            Dim printArea As String
            printArea = "A:W"
            For i = 1 To numPages - 1
                printArea = printArea & ",AA" & (2 + (i * 6)) & ":AD" & (7 + (i * 6))
            Next i
            printRange.Worksheet.PageSetup.PrintArea = printArea
           

            printRange.ExportAsFixedFormat Type:=xlTypePDF, FileName:=pdfName, Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
           

            printRange.Worksheet.PageSetup.PrintArea = "A:W"
        End If
    Next ws
End Sub

 

[noveske]

Figured it out:

Flipped it around a bit.

Make it so if there's a value in AA14, then set print range to A1:W105.
Value in AA8, then set print range to A1:W73.
Else, set print range to A1:W37.

This will only work if print range never have to go over 4 pages.

 

[Peter_SSs]

Good to hear you worked it out.
If you would like to post the final solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[noveske]

Good to hear you worked it out.
If you would like to post the final solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

Sorry about that. Computer here is so buggy. Double posts and drafts keep changing up on me. I'll be sure it's included.

Sub SaveAsPDF()
    Dim ws As Worksheet
    Dim pdfName As String
    Dim printRange As Range
    Dim outputPath As String
    
    outputPath = ThisWorkbook.Path & "\Legal\"
    
    If Dir(outputPath, vbDirectory) = "" Then
        MkDir outputPath
    End If
    
    For Each ws In ThisWorkbook.Sheets(Array("F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P"))
        If Application.WorksheetFunction.CountA(ws.Range("AA2:AA7")) > 0 Then 
            If Not IsEmpty(ws.Range("AA14")) Then
                Set printRange = ws.Range("A1:W105")
            ElseIf Not IsEmpty(ws.Range("AA8")) Then
                Set printRange = ws.Range("A1:W73")
            Else
                Set printRange = ws.Range("A1:W37")
            End If
            
            pdfName = outputPath & ws.Name & ".pdf"
            

            printRange.ExportAsFixedFormat Type:=xlTypePDF, FileName:=pdfName, Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End If
    Next ws
End Sub