Turn off sheet tabs & export data . Help me!

[Mark Milan]

Hello,

I created a Dashboard made up of many worksheets.
For aesthetic choice, I decided to turn off sheet tabs. To do that --> Click on "Options" in the left-hand menu -->In the Excel Options dialog box, click on "Advanced" in the left-hand menu --> Scroll down to the "Display options for this workbook" section --> Uncheck the "Show sheet tabs" option

Now, I inserted a macro to copy a Pivot Table and Paste into New Sheet (There are tens of pivot table in each sheet; if you select a cell within one of those pivot tables and then click on a button, selected pivot table will be paste into new sheet). The 1st problem I got is that I need the pivot table to be paste in another workbook!

Moreover, when you double-click on amounts in a pivot table, you know that a new sheet is created with the records used in that total. That is a very useful Excel's DrillDown (Show Details) feature. But I turn off the sheet tabs !

Do you know how to export pivot table, including data and formatting into a new book???

I'm going crazy

Thank you
Mark