Print Loop to Single PDF

[KAJ31]

I'm trying to create a macro that will loop through my 30 divisions on 4 different tabs and print all to a single pdf (ie 120 pages). Currently its runs through the loop but only combines the LAST division in the loop to the PDF (4 pages only . I'd like to change the macro to print as SINGLE pdf. Thanks!

Sub TestPrintR1()

Dim i As Integer
i = 1

Do Until i > 29

Sheets(Array("Div", "Historical R12 - Division", "Hourly - Division", "Forecast - Division")).Select
Range("q3").Select
ActiveCell.FormulaR1C1 = "=Print!R[" & i - 1 & "]C[-14]"
Range("q4").Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FILENAME:= _
"Q:\Lewis Tree Service\Finance\FP&A\Monthly Statements\Test Region 1" & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False

i = i + 1
Loop

 

[jolivanes]

Put your code between code tags. Select (Highlight) your code and click on the < vba / > (without spaces) at the top of your window.
The array only has four sheets.
Excel does not have "divisions". Explain in Excel speak.
Explain exactly what you want to happen in a concise manner.
Make sure all the sheets have the same Print Quality.

Sub What_Is_It()
Dim Sheet As Worksheet
For Each Sheet In ActiveWorkbook.Sheets
    MsgBox Sheet.Name & " Print Quality = " & Sheet.PageSetup.PrintQuality(1)
Next Sheet
End Sub

 

[KAJ31]

What I am trying to do is generate a single PDF that will be 120 Pages
Sheets to print are: "Div", "Historical R12 - Division", "Hourly - Division", "Forecast - Division"
Each Sheet has a Dropdown in Cell Q3 that contains 30 divisions.
This macro runs through the print loops but the PDF that is saved is only for the LAST division in the loop. I'm thinking I may need to append the PDF or something...

Sub TestPrintR1()
    
    Dim i As Integer
    i = 1
    
    Do Until i > 30

     Sheets(Array("Div", "Historical R12 - Division", "Hourly - Division", "Forecast - Division")).Select
    Range("q3").Select
      ActiveCell.FormulaR1C1 = "=Print!R[" & i - 1 & "]C[-14]"
    Range("q4").Select
    
   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FILENAME:= _
        "Q:\Test Region 1" & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False
        
     i = i + 1
    Loop
        
End Sub

 

[jolivanes]

Re: Each Sheet has a Dropdown in Cell Q3 that contains 30 divisions.
Are all of these divisions on the one sheet? Like pages of the one sheet?
If so, is the print range set to the whole page?

 

[KAJ31]

Yes all of the division are listed in rows in a sheet (the "Print" sheet")
The "Div" tab is a statement with the same rows & columns and the data changes based on the dropdown for the divison
The print range is set to the whole page

 

[jolivanes]

Beyond me what you want.

 

[John_w]

To achieve the desired output of a single PDF with multiple pages, one page for each sheet and dropdown value combination, you have to copy the output of every combination to a single sheet and insert page breaks.

This macro assumes the dropdowns are in-cell data validation dropdowns.

Public Sub Create_Multiple_Pages_PDF()

    Dim PDFfullName As String
    Dim PDFsheet As Worksheet
    Dim destCell As Range
    Dim wsName As Variant
    Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range
    Dim copyRange As Range
    
    PDFfullName = ActiveWorkbook.Path & "\120 pages.pdf"
   
    'Add temporary sheet for PDF output
    
    With ActiveWorkbook
        Set PDFsheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
    End With
    Set destCell = PDFsheet.Range("A1")
    
    'Loop through specified sheets
    
    For Each wsName In Array("Div", "Historical R12 - Division", "Hourly - Division", "Forecast - Division")
    
        'Cell containing data validation in-cell dropdown
    
        Set dataValidationCell = ActiveWorkbook.Worksheets(wsName).Range("Q3")
         
        'Source of data validation list
        
        Set dataValidationListSource = Evaluate(dataValidationCell.Validation.Formula1)
         
        'Set each data validation value in this sheet to update sheet cells
        
        For Each dvValueCell In dataValidationListSource
        
            dataValidationCell.Value = dvValueCell.Value
            
            'Copy sheet cells to next cell in temporary PDF sheet
            
            Set copyRange = dataValidationCell.Worksheet.UsedRange           
            dataValidationCell.Worksheet.Activate
            copyRange.Select
            Selection.Copy
            PDFsheet.Activate
            destCell.Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
        
            'Add page break and update destination cell
            
            With PDFsheet
                .HPageBreaks.Add Before:=.Rows(.UsedRange.Rows.Count + 1)
                Set destCell = .Cells(.UsedRange.Rows.Count + 1, 1)
            End With
        
        Next
        
    Next
    
    'Save temporary sheet as .pdf file
    
    PDFsheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfullName, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    
    'Delete temporary sheet
    
    Application.DisplayAlerts = False
    PDFsheet.Delete
    Application.DisplayAlerts = True
    
End Sub