Pivot table - cell format to remain the same when refresh

[mingali]

Hi,

I have generated a few pivot tables and format the cells to the way i prefer in the same workbooks. But whenever i change the field data, the format will back to normal.

1) Is it possible to have the format remain the same when data has changed? If so, can someone please write me a VBA code so that all the pivot tables have the same type format cells?

2) In the attached file (has been paste value already), it can be seen that only a few cost centres has cost.in the actual file, column B is in a collapse state, ie details in columns C & D are hidden. I would like to have a control box to expand the those cost centres with amount in it. Would someone please help me to write a VBA code?

Thanks in advance for all the constructive input.

[Cheeky Charlie]

1) No need for VBA, just change the number format in the field settings (right click a number in the field and click field settings)

2) You can double-click the entries in column B to expand/collapse the groups - VBA could be written but would rely on the field names being identical each time, or being in the same order

[mingali]

i have generated quite a number of pivot tables. and i find it troublesome to reformat the cell when i change the data, say from July Actual to Aug Actual, etc. i am the one who to generate and tidy up the report for the whole team to view and prepare analyse work. i strongly believe VBA code will help me to reduce the time for the same task greatly, esp when the time is tight.

Thanks

[Cheeky Charlie]

You seem to be talking about something slightly different:

First description:
My existing pivot tables change number format when I alter the source data
Solution - use the pivot table field setting number format

Second description:
I create lots of pivot tables and always need to format them the same way which takes time
Solution - use a standard format, or possibly a macro, but you'd need to be more specific about what format to apply, for example to the whole table, or just one field, which field, what number format

Is it either of these, or something else?

[mingali]

First & Second descriptions meant the same: change data in pivot table. never mention 'add' nor 'chart'.

Thanks for your input.

[Cheeky Charlie]

I've changed my post to make it make more sense. I didn't write two descriptions of the same thing, try reading them again.