0

I need to create a report (for printing) that has two sections. The top section contains the scorecard, and the bottom section contains the drillthrough information for any "red" items in the scorecard.

The scorecard is currently a PowerPivot table with conditional formatting. If the scorecard has 3 "red" cells, then I can double-click on each of the red cells to drillthrough to the details. However, I would like to display those 3 drillthrough tables in the same worksheet below the scorecard, so I can print both the scorecard and the drillthrough details all at once.

I'm looking for a solution that is automated. Each time I print out the report, I can do a lot of copy and paste, but I rather not. I'm looking for a solution that is as automated as possible - so the bottom section of the report will always contain details for anything that is red in the scorecard. I'm open to using VBA.

Thanks!

Tallie
  • 1
  • 1

1 Answers1

0

I would suggest to use VBA. However first store a list of sheets in one of your sheets for your VBA code to use. Create the following subroutine in your Workbook code section to have the trigger for when a new sheet is selected:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Now add code to this subroutine to verify if the sheet selected was already present in your workbook by checking against the list.

If it wasn't in the list yet, check if there is a table on the sheet.

If there is a table on the sheet have its content copied/moved to your report sheet to a specific range and delete the drill through sheet again (and remove it from the list). Have the code select your report sheet again! If there was NO table on the sheet add it to the list of sheets

K_B
  • 3,668
  • 1
  • 19
  • 29