Dynamic print areas to pdf

[Chuck6475]

I know just enough VBA to be dangerous. I have a generalized spreadsheet with a summary report page. The data to be printed from the summary page varies. Therefore I'd like to build the PrintArea dynamically. As usual I've found several helpful hint on the web but can seem to tie it all together.

My plan was to use Chip Pearson's UNION2 function to test each potential print area (range) and combine them into one string. There are well over 60 different print areas / ranges on the summary sheet.

        Function Union2(ParamArray Ranges() As Variant) As Range
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Union2  - Chip Pearson
    ' A Union operation that accepts parameters that are Nothing.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim N As Long
        Dim RR As Range
        For N = LBound(Ranges) To UBound(Ranges)
            If IsObject(Ranges(N)) Then
                If Not Ranges(N).cells(2,1) Is Nothing Then    ' modified by user
                    If TypeOf Ranges(N) Is Excel.Range Then
					 
                        If Not RR Is Nothing Then
                            Set RR = Application.Union(RR, Ranges(N))
                        Else
                            Set RR = Ranges(N)
                        End If
                    End If
                End If
            End If
        Next N
        Set Union2 = RR
    End Function

I think this should work if I can only figure out how to get the ranges to pass to the function properly. I've tried them as a string or ranges and an array of ranges. Of course, the key word is tried as I'm not really sure what I'm doing.

Here are samples of the ranges I want to test.

Payouts!$A4$d20 Payouts!$F4$i20 Payouts!$K4$n20
Payouts!$A21$d37 Payouts!$F21$i37 Payouts!$K21$n37
Payouts!$A38$d51 Payouts!$F38$i51 Payouts!$K38$n51
Payouts!$A52$d65 Payouts!$F52$i65 Payouts!$K52$n65
Payouts!$A66$d79 Payouts!$F66$i79 Payouts!$K66$n79
Payouts!$A80$d93 Payouts!$F80$i93 Payouts!$K80$n93
Payouts!$A94$d99 Payouts!$F94$i99 Payouts!$K94$n99

 

[shg]

You know that each area (each cell in your example) will print on a separate page? If you want to print them to a single page, hide the unneeded rows and columns, then print A3:N99.

 

[Chuck6475]

You know that each area (each cell in your example) will print on a separate page? If you want to print them to a single page, hide the unneeded rows and columns, then print A3:N99.

Wow, thanks for the quick response. Yes, the plan is for them to print on separate pages (at least in most cases). Actually the plan is they produce separate pages within a single PDF which is then sent to Acrobat to run as a slideshow. As more data is added to the spreadsheet and additional areas develop data, then they will be added to the slide show.

 

[shg]

Maybe (untested)

  Dim wks As Worksheet

  Set wks = Worksheets("Payouts")

  With wks
    .PageSetup.PrintArea = "A4:D99, F4:I99, K4:N99"
    .ResetAllPageBreaks
    .HPageBreaks.Add Before:=Rows(21)
    .HPageBreaks.Add Before:=Rows(38)
    .HPageBreaks.Add Before:=Rows(52)
    ' ...
  End With

 

[Chuck6475]

shg

Thanks for your prior recommendation sorry it has taken so long for me to be able to implement them.

As I have several non-contiguous print areas I did some reconfiguring. The following code builds the print areas fine, however when I try to put in the Horizontal page breaks I get an error message, which I don't seem to be able to overcome. (Run-time 1004: Application-defined or object-defined error)

I also wrote the temporary data to a spreadsheet so I could check it easier, when it works I'll move that into the arrays.

Here is the code:

Public Sub Create_PDF()
Dim WS As Worksheet, temp As Long, j As Integer
Set WS = Worksheets("ColformResult")
    ' figure out what has to be printed from "ColFormResult" worksheet
    ResultsPrintAreaBuild
    
         Application.PrintCommunication = False
    With WS
        .PageSetup.PrintTitleRows = ""
        .PageSetup.PrintTitleColumns = ""
        

        
        .PageSetup.Orientation = xlLandscape
        .PageSetup.Zoom = 205
        

        tempString = ColFormPA()
        Debug.Print (tempString)
        Debug.Print (Len(tempString))
        .PageSetup.PrintArea = tempString
        .ResetAllPageBreaks

            FinalRow = .Cells(Rows.count, 12).End(xlUp).Row
            ' insert pagebreaks
            For j = 4 To FinalRow
                temp = .Cells(j, 12)
            
           .HPageBreaks.Add Before:=Rows(temp)
            Next j

    Application.PrintCommunication = True
    End With

    PDFPrintAll



End Sub

 

[Chuck6475]

It is amazing what some sleep will do for you. I figured it out in the middle of the night.

My routine tried to insert a pagebreak at the beginning of an area to print instead of in the middle. i.e. a25:d60 - trying to place a pagebreak at a25 gives an error. Placing one at a50 does not.