Solved

Hide or remove columns in output

Posted on 2015-02-12
7
75 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
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.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

911 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

26 Experts available now in Live!

Get 1:1 Help Now