CAN VBA SORT PIVOT TABLE BY ANOTHER FIELD VALUE?

[nitrammada]

Hi Everyone,
Does anyone know how to sort a field (Descriptions) by another field (Sort) in a pivot table without having to place the field (Sort) in the 'Rows' or 'Values' box? Can this be done with VBA?

To explain:
The field (Descriptions) has 1000's of rows so sorting by custom list will not work coupled with the fact that each report has different descriptions in differing order.
Sorting A-Z will not work as the sequence I want the Descriptions to appear, is not alphabetical.

I have indexed each Description with a field called ‘Sort’ which is essentially a row number, this is the order I need the descriptions to appear in. If put the design of the pivot table in Tabular Form, and insert the field (Sort) as a row, then yes, I can sort by that column and get something like this with the Descriptions in the correct order:

H1Desc	H2Desc	H3Desc	H5Desc	H6Desc	Sort	Descriptions	Unit	Quantity
NEUTRAL BAY	CLIENT COSTS						%	0.08
	COMMERCIAL							9.00
	ESCALATION						%	0.07
	EXPECTED						%	0.04
	NET TRADE	BASEMENT	CEILING	False Ceilings	556021	Note: CON101 -	Note	0.00
					556022	Basement 1		0.00
					556023	CLG-102 - 13mm	m2	25.00
					556024	Insulation to s	m2	25.00
					556025	450x450mm Acces	No	1.00

Without the Sort field in the pivot table, I get this:

H1Desc	H2Desc	H3Desc	H5Desc	H6Desc	Descriptions	Unit	Quantity
NEUTRAL BAY	CLIENT COSTS					%	0.08
NEUTRAL BAY	COMMERCIAL						9.00
	ESCALATION					%	0.07
	EXPECTED					%	0.04
	NET TRADE	BASEMENT	CEILING	False Ceilings	450x450mm Acces	No	1.00
					Basement 1		0.00
					CLG-102 - 13mm	m2	25.00
					Insulation to s	m2	25.00
					Note: CON101 -	Note	0.00

You can see above, the order of the Descriptions field is incorrect.

I need the layout in 'Compact Form', however, when I change to 'Compact Form' I get a result like this:

Row Labels	Unit	Quantity	Rate	Total
NEUTRAL BAY PROJECT
CLIENT COSTS	%	0.08	45,753,089	3,660,247
COMMERCIAL IMPACT AS ADVISED BY CLIENT		9.00	7,000	21,000
ESCALATED TRADE VALUE	%	0.07	85,519,793	2,993,193
EXPECTED CONSTRUCTION TENDER AMOUNT	%	0.04	55,258,934	2,210,357
NET TRADE VALUE
BASEMENT
CEILING
False Ceilings
556021
Note: CON101 -	Note	0.00	0	0
556022
Basement 1		0.00	0	0
556023
CLG-102 - 13mm	m2	25.00	85	2,125
556024
Insulation to s	m2	25.00	35	875
556025
Note: CON101 -	No	1.00	450	450

You can see above, that the Sort field occupies a row. Even if I hide the field in Tabular Form and then change to Compact Form, the Sort field still occupies a row, except that it’s blank. I have tried with changing the height of the row to 1, and it looks ok, but it’s a cumbersome way to do it and as I’m designing this pivot table for 100’s of end users, it can’t be this complex.

What I'm after is a report like this.

Is there any way of achieving this with perhaps, VBA? Where the VBA code will sort the 'Descriptions' field by the 'Sort' field, as custom list and A-Z sorting will not achieve the above.
I would be very grateful for any thoughts or suggestions you may have.
Thanks in advance
Adam