Month Gross Consumble Custom Clearing Battery
Apr-05 3187 3187 0 0 0
Apr-05 44779 0 300 44479 0
May-05 18900 0 18900 0 0
May-05 13087 0 0 0 13087
Jun-05 9900 0 0 0 9900
Jun-05 29856 0 29856 0 0

If I Insert Pivot table & select Month as Row label & Select Gross,
Consumables, Custom etc as sum Values, the column heading shows "Sum of
Gross", "Sum of Consumables". Is there any facility to design the column
heading without the words"Sum of" & retain the original headings in the pivot
table. This will help me to copy the pivot table values.?

Hi Hemant, I am not 100% sure what your question is as you seem to contract
yourself when you say:-

"Is there any facility to design the column heading without the words"Sum
of" & retain the original headings in the pivot table. "

You can certainly overwrite the column headings in the Pivot Table by
clicking in the Column Heading field and replacing it with something else.

For example you could replace your Sum of Gross with the letter B.

Not sure if this answers your question though.

If my comments have helped please hit Yes.

Thanks.



"Hemant" wrote:

Month Gross Consumble Custom Clearing Battery
Apr-05 3187 3187 0 0 0
Apr-05 44779 0 300 44479 0
May-05 18900 0 18900 0 0
May-05 13087 0 0 0 13087
Jun-05 9900 0 0 0 9900
Jun-05 29856 0 29856 0 0

If I Insert Pivot table & select Month as Row label & Select Gross,
Consumables, Custom etc as sum Values, the column heading shows "Sum of
Gross", "Sum of Consumables". Is there any facility to design the column
heading without the words"Sum of" & retain the original headings in the pivot
table. This will help me to copy the pivot table values.?

Hi Hemant, I have just put your example up on:-

www.pierrefondes.com

(top item on the home page)

- to show you what I have done / mean.

Maybe this helps you / or others to be able to answer your question?

If my comments have helped please hit Yes.

Thanks.



"Hemant" wrote:

Month Gross Consumble Custom Clearing Battery
Apr-05 3187 3187 0 0 0
Apr-05 44779 0 300 44479 0
May-05 18900 0 18900 0 0
May-05 13087 0 0 0 13087
Jun-05 9900 0 0 0 9900
Jun-05 29856 0 29856 0 0

If I Insert Pivot table & select Month as Row label & Select Gross,
Consumables, Custom etc as sum Values, the column heading shows "Sum of
Gross", "Sum of Consumables". Is there any facility to design the column
heading without the words"Sum of" & retain the original headings in the pivot
table. This will help me to copy the pivot table values.?

Use a Macro to fix Field Names in a Pivot Table.

If you have lots of Data field names to change you could use a macro, to
make the job easier. For example, the following macro will change all the
Data field captions in the first pivot table on the active sheet. i.e. €œSum
of Units€�, changes to €œUnits €�, leaving an extra space after the original
source field name.
__________________________________________________ _
Sub ChangeCaptions()
€˜
€˜code source: www.pivot-table.com
€˜
Dim pf As PivotField
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
For Each pf In pt.DataFields
pf.Caption = pf.SourceName & " "
Next pf

End Sub
__________________________________________________ _

Copy & paste the code above into any one of your macro modules. Also the
Sub name (macro name) can be renamed, as long as its a single word
(PivotFieldNameChg).

If you prefer to have the space at the beginning of the field name, change
the code.
From: pf.Caption = pf.SourceName & " "
To: pf.Caption = " " & pf.SourceName

--
Kevin


"Hemant" wrote:

Month Gross Consumble Custom Clearing Battery
Apr-05 3187 3187 0 0 0
Apr-05 44779 0 300 44479 0
May-05 18900 0 18900 0 0
May-05 13087 0 0 0 13087
Jun-05 9900 0 0 0 9900
Jun-05 29856 0 29856 0 0

If I Insert Pivot table & select Month as Row label & Select Gross,
Consumables, Custom etc as sum Values, the column heading shows "Sum of
Gross", "Sum of Consumables". Is there any facility to design the column
heading without the words"Sum of" & retain the original headings in the pivot
table. This will help me to copy the pivot table values.?

Hi
Thanks for your reply. But It wont work for number of columns.
However I have received answer from AFSSKier, I will try the same.
Hemant.

"trip_to_tokyo" wrote:

Hi Hemant, I have just put your example up on:-

www.pierrefondes.com

(top item on the home page)

- to show you what I have done / mean.

Maybe this helps you / or others to be able to answer your question?

If my comments have helped please hit Yes.

Thanks.



"Hemant" wrote:

Month Gross Consumble Custom Clearing Battery
Apr-05 3187 3187 0 0 0
Apr-05 44779 0 300 44479 0
May-05 18900 0 18900 0 0
May-05 13087 0 0 0 13087
Jun-05 9900 0 0 0 9900
Jun-05 29856 0 29856 0 0

If I Insert Pivot table & select Month as Row label & Select Gross,
Consumables, Custom etc as sum Values, the column heading shows "Sum of
Gross", "Sum of Consumables". Is there any facility to design the column
heading without the words"Sum of" & retain the original headings in the pivot
table. This will help me to copy the pivot table values.?

Hello,
Thank you for the reply. Though I am not wellversed with the Macros, I will
try & let you know. I hope it will work.
Hemant


"AFSSkier" wrote:

Use a Macro to fix Field Names in a Pivot Table.

If you have lots of Data field names to change you could use a macro, to
make the job easier. For example, the following macro will change all the
Data field captions in the first pivot table on the active sheet. i.e. €œSum
of Units€�, changes to €œUnits €�, leaving an extra space after the original
source field name.
__________________________________________________ _
Sub ChangeCaptions()
€˜
€˜code source: www.pivot-table.com
€˜
Dim pf As PivotField
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
For Each pf In pt.DataFields
pf.Caption = pf.SourceName & " "
Next pf

End Sub
__________________________________________________ _

Copy & paste the code above into any one of your macro modules. Also the
Sub name (macro name) can be renamed, as long as its a single word
(PivotFieldNameChg).

If you prefer to have the space at the beginning of the field name, change
the code.
From: pf.Caption = pf.SourceName & " "
To: pf.Caption = " " & pf.SourceName

--
Kevin


"Hemant" wrote:

Month Gross Consumble Custom Clearing Battery
Apr-05 3187 3187 0 0 0
Apr-05 44779 0 300 44479 0
May-05 18900 0 18900 0 0
May-05 13087 0 0 0 13087
Jun-05 9900 0 0 0 9900
Jun-05 29856 0 29856 0 0

If I Insert Pivot table & select Month as Row label & Select Gross,
Consumables, Custom etc as sum Values, the column heading shows "Sum of
Gross", "Sum of Consumables". Is there any facility to design the column
heading without the words"Sum of" & retain the original headings in the pivot
table. This will help me to copy the pivot table values.?