Hide or remove columns in output

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
Marcell999Asked:
Who is Participating?
 
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
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
 
James ElliottManaging DirectorCommented:
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
 
Marcell999Author Commented:
Hi James,

What would you suggest?

Marcell
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
Marcell999Author Commented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
Marcell999Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.