I've done lots of searching but have been unable to find a solution, so I thought I'd post this small project to see if anyone is interested in attacking it.
In Excel, my overall goal is to create 1 continuous report (PDF) using many tables/charts from many different worksheets. The KEY is I want all the worksheet content (tables/charts), all from different worksheets, to be presented continuously (one table/chart after another & fitting as many as possible on each page).
There will be a GUI that gives users the option to either select 1) the DEFAULT ORDER of combining all worksheet content -or- 2) MANUALLY SELECTING/CHOOSING what sheets will be included in the report and how those sheets will be organized (i.e., what sheets are displayed 1st, 2nd, 3rd, etc). See an example of what this GUI could look like in the attached EXCEL file. I'm flexible on how this GUI actually looks like -- I just created a ugly example for demo purposes.
See attached PDF of what the report would look like based on what the user Choose/Selected. Note the organization of the sheet content (what is displayed 1st, 2nd, 3rd, 4th). Also note that what I attached is only for example purposes and not the actual Workbook.
My actual workbook will contain:
* ~10-15 worksheets
* each worksheet is very unique in terms of table formatting (merged cells, dimensions of cells, etc). I'd like to keep the formatting the same (as it is displayed in excel).
* different content (tables/charts) per worksheet and the combination of the content varies in size. Some sheets contain tables that are only 3-4 rows/columns, other sheets will have combined content (tables/charts) that take up 3/4ths of a page.
Finally, excel solution must work in Excel 2010 or greater.