Pivot Table - Hide Columns + Maintain Grand Total

[philipq]

Greetings Friends,

I have been doing a bit of coding in VBA on some reports for the place I work.
I am already 90% through and up with this seemingly small challenge.

I need to display only one of the column value (count) and yet show the sum of all column values.
In other words I need to have the columns hidden for the other columns but total should show that of all.

Could anyone explain if this can be done via VBA.
Greatly appreciate your help.

Sample data as below:
I need to diplay column labeled '2' only and hide all other possibilities and yet keep grandtotal values the same.

Calls Handled	Column Labels
Row Labels	1	2	3	4	Grand Total
ALA-ARA-SER	340	831		11	1182
ALA-ENG-SER	80	171		2	253
ALQ-ARA-SER	41	119			160
ALQ-ENG-SER	179	483			662
CEN-ARA-SER	866	2658	1	80	3605
CEN-ENG-SER	861	3595		50	4506
DIP-ENG-SER	5	2			7
FUJ-ARA-SER	29	55			84
FUJ-ENG-SER	9	7			16
GAR-ARA-SER	232	626		9	867
GAR-ENG-SER	319	901		1	1221
MUS-ARA-SER	783	1968		35	2786
MUS-ENG-SER	726	2016		31	2773
QAB-ARA-SER	65	145		1	211
QAB-ENG-SER	209	412			621
RAK-ARA-SER	212	358		6	576
RAK-ENG-SER	61	107			168
SHJ-ARA-SER	287	696		6	989
SHJ-ENG-SER	246	570			816
SZR-ARA-SER	498	1459		32	1989
SZR-ENG-SER	1452	4481		4	5937
Grand Total	7500	21660	1	268	29429

<tbody>
</tbody>

 

[Andrew Poulsom]

Welcome to MrExcel.

A pivot table will only sum what's in it. So what you want to do isn't possible either in the user interface or in VBA. That said you could hide the unwanted columns on the worksheet.

 

[philipq]

Yes, you are correct.
So I only want certain columns hidden.
However, when columns are hidden the pivot table totals only what is displayed.
But in effect pivottable object still has all the hidden data available to it, doesn't it?
It should be able to calculate the total of all coumns and display it maybe as a separate calculated column via VBA.
Do you think there could be such a possibility?

Don't mean to be hard headed here, just seeing if I could ease up the programming using excel methods rather than doing all those loops.

Thanks,
Philip

 

[Andrew Poulsom]

If you can't do it in the user interface you can't do it in VBA. A Calculated Field would apply to each of the visible fields.

 

[philipq]

Well I guess then the only way to go about it is to copy the pivottable and paste it as values.
Search-up the unwanted columns and delete them.
This way the grand totals would not change.
Any better suggestions?

 

[Andrew Poulsom]

Hide the columns that you don't want to see in the worksheet?

 

[philipq]

Yes true & simple, however, this report goes to the higher management who wants things in a prescribed format.
They may forward the data over to others and therfore would not want unwanted data available even though hidden.