Solved

Hide or remove columns in output

Posted on 2015-02-12
7
77 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

832 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