report - Show multiple drillthrough tables in a single Excel sheet -

i need create report (for printing) has 2 sections. top section contains scorecard, , bottom section contains drillthrough information "red" items in scorecard.

the scorecard powerpivot table conditional formatting. if scorecard has 3 "red" cells, can double-click on each of red cells drillthrough details. however, display 3 drillthrough tables in same worksheet below scorecard, can print both scorecard , drillthrough details @ once.

i'm looking solution automated. each time print out report, can lot of copy , paste, rather not. i'm looking solution automated possible - bottom section of report contain details red in scorecard. i'm open using vba.

thanks!

i suggest use vba. first store list of sheets in 1 of sheets vba code use. create following subroutine in workbook code section have trigger when new sheet selected:

private sub workbook_sheetchange(byval sh object, byval target range) 

now add code subroutine verify if sheet selected present in workbook checking against list.

if wasn't in list yet, check if there table on sheet.

if there table on sheet have content copied/moved report sheet specific range , delete drill through sheet again (and remove list). have code select report sheet again! if there no table on sheet add list of sheets