Macro is changing a column width and format and I don't know why.

[Cidona]

Dear Forum.

Sorry to be on here twice on the one day but trying to finish this spreadsheet off and am having an issue with the 'simple' macros I've tried to set up.

Background...So I started manipulating the 'Personal Expenses Calculator' template (which mikeTRON was good enough to help me out with a pivot table question with). I have things set up the way I want however I tried to create macros that would make it easier to update the Pivot Tables (as my father will be using this and is not very computer savvy).

There are two Pivot Tables in the spreadsheet (one on 'ExpenseBreakDn' sheet and another on 'CashFlow' sheet). Both Pivot tables are getting their data from the 'Expense Log' sheet. There are two macros. One is assigned to the 'Shape' on 'Expense Log' sheet with the text 'Expense Break Dn' and the other macro is assigned to the 'Shape' on the 'Expense Log' sheet with the text 'Cash Flow'.

The idea is that from the 'Expense Log' sheet that the user could click on either one of these 'Shapes' to activate the macro which would then update both Pivot Tables and bring the user to the relevant sheet (which sheet depending on which 'Shape' the user clicked on).

All seems well except for some reason it resizes the column C and removes the formatting (Currency - Euro). I had set up the columns with a width of 13 and set the currency format for the column. However when I run the macro it reduces the column to 10 and removes the '€' sign.

Any ideas why it is doing that? Please see attached file.

Thank you for any insight.

BTW the macro was created using the Macro Recorder.

[David A Coop]

Hi Cibona,

FIRST: For some reason, you have different formatting of the cells in Col C. viz_-"€"* #,##0.00_-;-"€"* #,##0.00_-;_-"€"* "-"??_-;_-@_-

The other cells appear to be Accounting Euro English (Ireland) If you change Col C to the accounting format, you will hang on to the Euro symbol.

SECOND: Go into Pivot Table Options. (Analyze Ribbon under PT Tools) and click in the box to remove the tick alongside Autofit column width on update

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[Cidona]

That worked perfect David.

Not sure why the formatting was different. I thought I had applied the formatting the same way (obviously didn't though )

Thank you very much for the prompt reply. I can go to sleep now

Best Regards.

[David A Coop]

You're welcome Cidona.

Happy to have been able to help.

Thanks for the Rep* - please remember to mark the thread as SOLVED