Marcell999
asked on
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
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
ASKER
Hi James,
What would you suggest?
Marcell
What would you suggest?
Marcell
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).Table Range2.Cop y .Range("A1")
.Move
With ActiveSheet.PivotTables(1)
Pivot_trans = .ColumnGrand
.ColumnGrand = True
.Parent.Cells.SpecialCells (xlCellTyp eLastCell) .ShowDetai l = True
Set ResultSheet = ActiveSheet
.ChangePivotCache ActiveWorkbook.PivotCaches .Create(xl Database, Selection, xlPivotTableVersion14)
.ColumnGrand = Pivot_trans
.Parent.Activate
End With
ResultSheet.Columns("H:K") .Delete
ResultSheet.Columns("D").D elete
End With
End Sub
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).Table
.Move
With ActiveSheet.PivotTables(1)
Pivot_trans = .ColumnGrand
.ColumnGrand = True
.Parent.Cells.SpecialCells
Set ResultSheet = ActiveSheet
.ChangePivotCache ActiveWorkbook.PivotCaches
.ColumnGrand = Pivot_trans
.Parent.Activate
End With
ResultSheet.Columns("H:K")
ResultSheet.Columns("D").D
End With
End Sub
ASKER
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?
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?
How about this:
Sub Button1_Click()
Dim Pivot_trans As Boolean
Dim ResultSheet
Dim PivotSheet
With Sheets.Add
Sheets("Pivot (3)").PivotTables(1).Table Range2.Cop y .Range("A1")
.Move
Set PivotSheet = ActiveSheet
With ActiveSheet.PivotTables(1)
Pivot_trans = .ColumnGrand
.ColumnGrand = True
.Parent.Cells.SpecialCells (xlCellTyp eLastCell) .ShowDetai l = True
Set ResultSheet = ActiveSheet
.ChangePivotCache ActiveWorkbook.PivotCaches .Create(xl Database, Selection, xlPivotTableVersion14)
.ColumnGrand = Pivot_trans
.Parent.Activate
End With
ResultSheet.Columns("H:K") .Delete
ResultSheet.Columns("D").D elete
Application.DisplayAlerts = False
PivotSheet.Delete
Application.DisplayAlerts = True
End With
End Sub
Sub Button1_Click()
Dim Pivot_trans As Boolean
Dim ResultSheet
Dim PivotSheet
With Sheets.Add
Sheets("Pivot (3)").PivotTables(1).Table
.Move
Set PivotSheet = ActiveSheet
With ActiveSheet.PivotTables(1)
Pivot_trans = .ColumnGrand
.ColumnGrand = True
.Parent.Cells.SpecialCells
Set ResultSheet = ActiveSheet
.ChangePivotCache ActiveWorkbook.PivotCaches
.ColumnGrand = Pivot_trans
.Parent.Activate
End With
ResultSheet.Columns("H:K")
ResultSheet.Columns("D").D
Application.DisplayAlerts = False
PivotSheet.Delete
Application.DisplayAlerts = True
End With
End Sub
ASKER
Phillip you are the man!!! Awesome solution!!!! Thank you?
One more question.
With the following part in the code: Sheets("Pivot (3)").PivotTables(1).Table Range2.Cop y .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...
One more question.
With the following part in the code: Sheets("Pivot (3)").PivotTables(1).Table
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You could, I suppose, turn off the ability to drill down into the data, and provide a seperate solution for getting the detailed data.