Pivot Table Drilldown - formatting, hidden columns, range instead of table

[PivotMeThis]

I find it highly annoying that Excel puts my data in a table with a bunch of filters when I drilldown in a pivot table. I always change it back to a range and make multiple changes to the formatting. If I have hidden columns in the base data they are no longer hidden in the worksheet which is created through the pivot table. It would be VERY nice if the new worksheet would be formatted like the base data...

Are there any advanced features I am missing to customize how the worksheet which is created by drilling down in a pivot table will appear? I have multiple workbooks that will be used by management and the data that is generated by the drilldown should appear formatted and ready for their use.

I thought about recording a macro to format everything and assigning it to a button but since the drilled down report will appear on a new worksheet I don't know where to put it or how to make it work. There could be multiple worksheets created by drilling down.

Thanks for any help out there.
I'm using 2010 - they are using 2007. Gotta love THAT!

 

[Jerry Sullivan]

Is the code not working when you put the module code in a separate module?

If not, what error or result is occurring?

 

[Casualneil]

That's the weird thing, i'm not getting an error code. When i drill down on the pivot that i have on sheet2 the formatting that i want to retain from the data set sheet is not pulling through. It's percentages that i'm trying to retain. As i say, i can get it to work on a clean workbook using the same data set on sheet1 and the pivot on sheet2.

 

[Jerry Sullivan]

Do you know how to put a debug Break into your code then step through the execution of each line? That process usually reveals the problem.

 

[MichaelJohnson]

Jerry, I know that this is an old link but I have be using the very useful drilldown formatting for some time now (Thank You!). My question is that I now have run into sheet that has ' and # in some column titles and they won't hide even though they are in the drilldown format table. Is there some way to get these to work. I can't get the column titles changed they are in an SAP exported sheet. It seems to involve a lot of VBA to change the titles on an import event. Sorry if I have posted this question in the wrong way, let me know how to if necessary.

 

[joaodesa]

Hello Jerry Sullivan. This post is incredible and for sure people should know about this. Congratulation on your effort!

I know that this thread has some years... but for some reason, I cannot get the code to work... perhaps it is related to OLAP cube model. Is it possible? Should I change something In the VBA code for it to work?

The column headers of the drilled table come in the following format:
BD_Consumos[Tipo Consumo]

Thank you a lot!

 

[joaodesa]

Hello again. Please forget about my last reply, there is already some information on this tread that I'll check with more detail. Thank you!.

 

[tanmayjalan]

Hi Jerry
Thanks for this amazing solution. Is there any way in your module to also resequence columns in the the drill down sheet? (Delete + resequence remaining ones)
I tried using the method provided on Link. I invoked the "colOrder" method in "Format_PT_Detail" right before "Call Format_Table(Tbl:=tblNew)" but then i loose all formatting.

 

[TheKingMe]

Hi Jerry,

Thank you very much for this great solution!

I have my data in a data model, and the drill-down pivot table therefore starts on cell A3 instead of A1, which gave an error. Very simple adjustment