Solved

Hide or remove columns in output

Posted on 2015-02-12
7
76 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

831 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