excel report generate with dynamic pages

Question

I'm trying to print a report in excel for every division of a company to get a single report containing the appendix reports of the sections below them. I.e. Human Resources Division has sections of recruiting, staffing, compliance, inclusion and diversity, leadership development, etc.

I want the report to have the static pages followed by the four dynamic pages of each of that division's sections behind it. Right now, only a single section is printed/generated when I run the code.

Sub GenerateReports()
Dim intRowDiv as Integer
Dim intRowSec as Integer

intRowDiv = 13
intRowSec = 13

Do While Sheets("LOOKUP_VALUES").Cells(2,2).Value = Sheets("LOOKUP_VALUES").Cells(intRowDiv,1).Value

Application.Calculate

'file naming
tempFileName = Sheets("LOOKUP_VALUES").Cells(7,2).Value & " - CLIMATE.pdf"
tempFileName = Replace(tempFileName, "/", "_")
tempFileName = Replace(tempFileName, "\", "_")
tempFileName = Replace(tempFileName, "&", "_")
tempFileName = Replace(tempFileName, ":", "_")

'Select Static Pages
Sheets(Array("2018 CoverPage", "intro", "table of contents", "division snapshot", "engagement snapshot", "action planning", "resources", "branch TOC", "branch intro")).Select

'Select Dynamic Pages
Sheets(2018 CoverPage").Activate

Do While Sheets("LOOKUP_VALUES").Cells(intRowSec, 4).Value <> ""

Sheets("LOOKUP_VALUES").Cells(8,2).Value = Sheets("LOOKUP_VALUES").Cells(intRowSec,4).Value

Sheets(Array("2018 Page10", "2018 Page11", "2018 Page12", "2018 Page13")).Select

'print to local location on comp
tempFileName = "C:\Users\sslattery\Documents\clim_reports\reports\" & TempFileName

'print full
ActiveSheet.ExportAsFixedFormat Type:xlTypePDF, Filename:= _
tempFileName _
, Quality:=xlQUalityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

'next section
intRowSec = IntRowSec + 1
Loop

'next division
intRowDiv = intRowDiv + 1
Loop

End Sub

A sample of your workbook might help, you are probably already aware of the Sheets(2018 CoverPage").Activate missing the initial ", also you are activating Sheets and selecting them but this could be achieved without any activation or selection, what I'm not understanding is the layout of the Sheet Lookup_values.... — Xabier

Vityata Vityata · Accepted Answer · 2017-12-04T16:24:58

If only one section is being printed, this means that your logic is broken somewhere. You have two loops - an inner and an outer loop. Make sure that each one is looping correctly with something as simple as this one:

Sub GenerateReports()
    Dim intRowDiv As Integer
    Dim intRowSec As Integer

    intRowDiv = 13
    intRowSec = 13

    Do While Sheets("LOOKUP_VALUES").Cells(2, 2).Value = Sheets("LOOKUP_VALUES").Cells(intRowDiv, 1).Value
        Debug.Print "Loop A " & Sheets("LOOKUP_VALUES").Cells(2, 2).Value
        Do While Sheets("LOOKUP_VALUES").Cells(intRowSec, 4).Value <> ""
            Debug.Print "Loop B " & Sheets("LOOKUP_VALUES").Cells(2, 2).Value
            intRowSec = intRowSec + 1
        Loop
        intRowDiv = intRowDiv + 1
    Loop

End Sub

You should get some information on the Immediate Window (press Ctrl + G to see it) and try to make sure that it is exactly the way you want it to be.

excel report generate with dynamic pages

1 Answers