Generate dynamic report using a template and cycle through data on the fly

[alexsheehan]

Hi everyone


What I have is an "Index" Worksheet and a SearchBox that the user can use to search my database for certain keywords (Performance Indicator data).


There is a cell underneath the SearchBox which contains a ValidationBox that is filtered/restricted to the values the user has searched for. I then have a hyperlink which allows the user to go directly to the currently selected indicator in the ValidationBox.


What I want to be able to do, is have a "Print" button on my "Index" page, which prints all of the Performance Indicators contained in my database that meet the criteria currently entered in the SearchBox.


I have a template report which I have already designed, but I want to be able to iterate through all the Performance Indicators one by one (that meet the SearchBox criteria) and Print them one by one, or better still, print them all as part of the same report, i.e. several separate pages, but "Page 1 of #".


Can this be done?


Does this make sense?


If I can clear it up a little (hopefully), I have designed a report which analyses one Performance Indicator at a time (analysis of one Indicator at a time is crucial), but I want to be able to dynamically fill this report with the first Indicator, then print, fill the report with the second indicator, then print, etc. etc. until all entries in the ValidationBox have been printed OR generate ONE BIG report on the fly which the user can then save as a new Workbook or PDF it etc.


Thanks everyone in advance - please let me know if this is achievable or not!

 

[Luke M]

Sounds like some type of looping code.

Do you know how many records meet your criteria? and/or is there a list somewhere?


Pseudocode will look like

[pre]

Sub PrintPages()
RecordCount = ListOfChoices.Count
For i = 1 to RecordCount
Indicator = ListOfChoices.Cells(1,0).Value
ReportCell.Value = Indicator
Worksheets("Report").Print
Next i
End Sub

[/pre]
or something like that...

 

[alexsheehan]

Thanks Luke,


Yes cell "C49" counts the number of records which meet the SearchBox criteria and the main Database list (the whole database) is in range "$A$51:$BE$127".


Unique Reference for each indicator starts in column "F" ("F51:F127").


The "Filtered List" (filtered from SearchBox criteria) is in range "A134:G209", column "E" ("E134:E209") contains my filtered list for the ValidationBox and cell "E134" is my ValidationBoxFirstValue.


Let me know if you need further details.

 

[alexsheehan]

I have decided to use the solution posted at

http://www.j-walk.com/ss/excel/tips/tip92.htm, however, although this works wonderfully well, I would like to add the feature to print all pages as part of the same document, i.e. not separate print jobs as currently available using this method.


Is there an easy way to do this?


Thanks for your help in advance guys


I have cross-posted this to http://chandoo.org/forums/topic/mail-merger

Alex