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!

 

[redjayhawk11]

I've followed what you posted and when I double click in the pivot I get an error and it takes me to "If rFieldsToKeep.Columns.Count > 1 Then" when I debug.

When you say "first place a list of fields that fields you want to keep in the drilldown detail in a one-column wide range, then add a workbook Name for that range." I wasn't 100% sure so what I did was insert a new "sheet" and named it "lstFieldsToKeep". I then took the fields that I wanted to keep and put them all in column A. When that failed and I got the error I then tried to put all the field names in row 1 which isn't what you said but assumed maybe I was misunderstanding.

I'm sure I'm missing something very basic but have been trying all morning to figure it out before I came back to ask for more help.

**After posting this I realized that I hadn't named the range "lstFieldsToKeep" and after naming the range the error never populated but all fields were showing instead of fields in my FieldsToKeep column on a separate sheet within the workbook.

 

[Jerry Sullivan]

While responding to a different thread on this topic, I found an error in my code in Post #40.

Modify the part shown in blue bold font shown here...

Public Sub KeepFields(ByVal rExistingData As Range, _
      rFieldsToKeep As Range)
'--This procedure removes all fields from rExistingData range
'  except those listed in rFieldsToKeep
      
 Dim vExistingData As Variant, vExistingFields As Variant
 Dim vFieldsToKeep As Variant, vResults As Variant
 Dim vMatchCol As Variant
 Dim lCol As Long, lRow As Long, lWriteCol
   
 '--validate input ranges
 If rFieldsToKeep.Columns.Count > 1 Then
   MsgBox "Fields to Keep range must be 1 column wide."
   Exit Sub
 End If
 
 '--read range values into arrays
 vExistingData = rExistingData.Value
 vFieldsToKeep = rFieldsToKeep.Value
 vExistingFields = Application.Index(rExistingData, 1, 0).Value
 If Not IsArray(vFieldsToKeep) Then _
   vFieldsToKeep = Array(vFieldsToKeep)
   
 ReDim vResults(1 To UBound(vExistingData, 1), _
   1 To UBound(vFieldsToKeep, 1))
 
 '--find each matching field and copy entire column of values
 For lCol = 1 To UBound(vFieldsToKeep, 1)
      vMatchCol = Application.Match(vFieldsToKeep(lCol, 1), vExistingFields, 0)
      If IsNumeric(vMatchCol) Then
         lWriteCol = lWriteCol + 1
         For lRow = 1 To UBound(vExistingData, 1)
            vResults(lRow, lWriteCol) = vExistingData(lRow, [B][COLOR="#0000FF"]vMatchCol[/COLOR][/B])
         Next lRow
      End If
 Next lCol
 
 '--clear existing data then write results
 With rExistingData
   .Clear
   If lWriteCol > 0 Then
      .Resize(, lWriteCol).Value = vResults
      '--convert to table (optional)
      ActiveSheet.ListObjects.Add SourceType:=xlSrcRange, _
         Source:=.Range("A1").CurrentRegion, _
         XlListObjectHasHeaders:=xlYes
   End If
 End With
 
End Sub

 

[Jerry Sullivan]

From the thread: http://www.mrexcel.com/forum/excel-questions/778468-modify-pivottable-double-click-behavior.html

It totally worked! I can't believe it. It is amazing so much code goes into executing what seems like a simple task.

My next issue is all of the date fields are formatted to general (eg 41927 - excels numerical date format) and I know it is easy to format to date, but I dont expect my bosses to do so or even know how.

So is there a way in the code to specify formatting of dates?

David, The quick and easy way to add that for a specific named field would be to add it to the code you used from Post #40 (with the correction in Post #42) like this...

Paste into the ThisWorkbook Module...

Private Sub Workbook_NewSheet(ByVal Sh As Object)
 Dim rDetail As Range, rFieldsToKeep As Range
 [COLOR="#0000CD"][/COLOR][COLOR="#0000CD"]Dim tblNew As ListObject[/COLOR]
 
 Set rDetail = Cells(1).CurrentRegion
 '--if new sheet is blank, rDetail.Count will be 1
 If rDetail.Count < 2 Then Exit Sub
 
 On Error Resume Next
 Set rFieldsToKeep = Range("lstFieldsToKeep")
 On Error GoTo 0
 If Err.Number <> 0 Then
   MsgBox "Named range: lstFieldsToKeep not found"
   Exit Sub
 End If

 '--Calls procedure to remove all fields
   '  except those listed in rFieldsToKeep
 Call KeepFields(rExistingData:=rDetail, _
   rFieldsToKeep:=rFieldsToKeep)

 [COLOR="#0000CD"]'--error handler used in case fields don't exist
 On Error Resume Next
 Set tblNew = Cells(1).ListObject
 If Not tblNew Is Nothing Then
   '--modify to match your field names and desired formats
   tblNew.ListColumns("[B]StartDate[/B]").Range.NumberFormat = [B]"m/d/yy" [/B]
   tblNew.ListColumns("[B]EndDate[/B]").Range.NumberFormat = [B]"m/d/yy" [/B]
  '--specify any other numberformats to change
   '...
   '...
 End If
 On Error GoTo 0[/COLOR]
End Sub

If you were wanting to match all the source data numberformats, without hard coding specific field names, you could merge the code shown in Post #2 of this thread instead of the modifications shown above.

 

[jseaz73]

I really like what you have accomplished here. I am trying to expand it to make use of conditional formatting.

To explain and simplify:
I have data in Column A and Binary (0/1) value in Column B.

I evaluate the data in B1 for a 1 and IF B1 = 1 then Highlight A1 "yellow".


In truth I have 6 other similar column pairs that I want to do this and I have not been able to figure out to make a VB code set that will allow for the formatting to occur in the vb code.


Where am I missing the mark ? Can it be done?

 

[mhenninger]

Hello, being a newbie to VB and rusty on programming... I need a little assistance... I have a spreadsheet of data that I am auditing for Change Requests in my organization monthly. The data presented are for several different Owners (LAN, WAN, Desktop, etc) ... I have the following columns: Change#, Owners, Date, Description, Implementation Plan, Backout Plan... I have over 200 changes to review each month. To make it easier, I decided to color code the Data fields that are in error with a Yellow fill... (this is the raw data) Once reviewed any errors are color-coded manually by my audit. I can easily present this data by filtering the cells by color and then grouping by the owners... BUT, when I try to create a report with a pivot table method to present my data I lose my fill-color formatting and when I drill-down into the pivot report How do I modify your VB scripts to maintain fill color, Grouping the report data by Owner and only displaying data that has a field that has a Yellow fill... To clarify, some rows can have 1 cell filled Yellow, some rows can have more (most will have none). The Pivot report is grouped by Owner....Thank you!

 

[Jerry Sullivan]

Hi jseaz73 and? mhenninger,

Your questions are so similar on this old thread. Are you the same person, or two people working on the same problem?

 

[mhenninger]

Hi jseaz73 and? mhenninger,

Your questions are so similar on this old thread. Are you the same person, or two people working on the same problem?

LOL Yes, we are two people working together for same solution... Thank you in advance for your assitance!

 

[Jerry Sullivan]

I don't think there's a simple way to do what you describe.

jseaz73 described there being 6 pairs of fields each consisting of a Binary (0/1) field and another field that would be colored yellow if the Binary field value is 1. If that's the setup, then it would be much simpler to use the 0/1 value to color cells in the drill down (perhaps using Conditional Formatting), than to try to read the formatting of the corresponding cells in the source data range. Could that work, or does the "manual" coloring of cells that mhenninger described prevent that approach?

Is their a field in the source data that can be used as a unique key for each row? For example is the Change# field one that would not have any duplicates in the source data range?

 

[mhenninger]

I don't think there's a simple way to do what you describe.

jseaz73 described there being 6 pairs of fields each consisting of a Binary (0/1) field and another field that would be colored yellow if the Binary field value is 1. If that's the setup, then it would be much simpler to use the 0/1 value to color cells in the drill down (perhaps using Conditional Formatting), than to try to read the formatting of the corresponding cells in the source data range. Could that work, or does the "manual" coloring of cells that mhenninger described prevent that approach?

Is their a field in the source data that can be used as a unique key for each row? For example is the Change# field one that would not have any duplicates in the source data range?

Jaeaz73 approach is the easiest design, but hardest to use when auditing the data.... My goal (I am the auditor for changes) is to look at each individual change (as many as 200) and then look for errors and mark the cells that have errant data... currently I code the cells with a color which writes to to the 6 pair fields with a "1" (they are set at zero)... I do this with a custom VB script and created a function I found "CountYellow"... the report works as required not using a Pivot table... But to enhance this report, it would be nice to crate a pivot report and drill down displaying the errors, but we can not find a way to carry the 0/1 data through the drill-down data to again to display the yellow highlighted fields...

 

[jseaz73]

I don't think there's a simple way to do what you describe.

jseaz73 described there being 6 pairs of fields each consisting of a Binary (0/1) field and another field that would be colored yellow if the Binary field value is 1. If that's the setup, then it would be much simpler to use the 0/1 value to color cells in the drill down (perhaps using Conditional Formatting), than to try to read the formatting of the corresponding cells in the source data range. Could that work, or does the "manual" coloring of cells that mhenninger described prevent that approach?

Is their a field in the source data that can be used as a unique key for each row? For example is the Change# field one that would not have any duplicates in the source data range?

Ok, so either would work for us. To answer your question, the Change number is unique and would only exists once in the source data. In the source data we would have two colors "Yellow" a warning and "Red" a failed audit.

We would like those colors to come through on the drill down.
SampleData
https://www.dropbox.com/s/yij1mgv90up45iy/SampleData.png?dl=0


Here