Solved

Hide or remove columns in output

Posted on 2015-02-12
7
73 Views
Last Modified: 2015-02-12
Hi,

Hope you can help, is there a way to hide/remove columns in pivot table's data output.

I have a data table with a few columns but would only like to show a set number of columns when I click on the show data button (see "Pivot (3)" sheet. I would only like to see data for column A;B;C;E;F;G (see green columns in sheet "Data") in the newly created workbook when I press the button.

Or if there is a way let me know.

Marcell
Track.xlsm
0
Comment
Question by:Marcell999
  • 3
  • 3
7 Comments
 
LVL 12

Expert Comment

by:James Elliott
ID: 40605335
Unfortunately, I don't think there's a way of doing this aside from limiting the data that is used to form the pivot table.

You could, I suppose, turn off the ability to drill down into the data, and provide a seperate solution for getting the detailed data.
0
 

Author Comment

by:Marcell999
ID: 40605339
Hi James,

What would you suggest?

Marcell
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40605351
My completely different suggestion:

Why not delete columns you don't want after they have been created? - like this:

Sub Button1_Click()

 Dim Pivot_trans As Boolean
 Dim ResultSheet
    With Sheets.Add
        Sheets("Pivot (3)").PivotTables(1).TableRange2.Copy .Range("A1")
        .Move
        With ActiveSheet.PivotTables(1)
            Pivot_trans = .ColumnGrand
            .ColumnGrand = True
            .Parent.Cells.SpecialCells(xlCellTypeLastCell).ShowDetail = True
           Set ResultSheet = ActiveSheet
            .ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, Selection, xlPivotTableVersion14)
            .ColumnGrand = Pivot_trans
            .Parent.Activate
        End With
           ResultSheet.Columns("H:K").Delete
            ResultSheet.Columns("D").Delete

   
    End With


End Sub
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Marcell999
ID: 40605361
Hi Phillip,

Your solution is good, but when I click on the new workbook pivot sheet it creates the same data again. Is there a way to remove the pivot sheet in the new workbook as part of this solution?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40605364
How about this:

Sub Button1_Click()

 Dim Pivot_trans As Boolean
 Dim ResultSheet
Dim PivotSheet
    With Sheets.Add
        Sheets("Pivot (3)").PivotTables(1).TableRange2.Copy .Range("A1")
        .Move
       Set PivotSheet = ActiveSheet
        With ActiveSheet.PivotTables(1)
            Pivot_trans = .ColumnGrand
            .ColumnGrand = True
            .Parent.Cells.SpecialCells(xlCellTypeLastCell).ShowDetail = True
           Set ResultSheet = ActiveSheet
            .ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, Selection, xlPivotTableVersion14)
            .ColumnGrand = Pivot_trans
            .Parent.Activate
        End With
           ResultSheet.Columns("H:K").Delete
            ResultSheet.Columns("D").Delete
          Application.DisplayAlerts = False
           PivotSheet.Delete
           Application.DisplayAlerts = True

   
    End With


End Sub
0
 

Author Comment

by:Marcell999
ID: 40605380
Phillip you are the man!!! Awesome solution!!!! Thank you?

One more question.

With the following part in the code: Sheets("Pivot (3)").PivotTables(1).TableRange2.Copy .Range("A1")

How do I know I know how to select the correct "TableRange" for a pivot if I would like to use this method on other charts with different pivots attached to them? I get the " Sheets("Pivot (3)").PivotTables(1)." part...
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40605381
I don't understand the question - but I suspect that is properly a separate question.

Could you please ask it in a new question - many thanks.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now