Pivot Table - Column formatting not preserved/extended when table has new rows

I feel that this is a question that must have been answered a thousand times, but I cannot find a solution that works.

I have a pivot table with a number of columns. Over time this pivot table will grow as new source data is added.

An example column looks like this;

Table Column2.png

I have applied conditional formatting so (blank) uses a Custom form of ;;; so making the fields truly blank. That all works. But, when I add some new data and refresh the pivot table the formatting is broken again. See the image. The new field is the one where the margin is shown as 0.37 and not 37.00%. Also, where the table has grown by one row the new row at the bottom shows (blank).

How do I ensure the numeric formatting and conditional formatting covers the whole table (column) as the table grows?

I am using a macro to refresh the pivot table as I want to use a single button to refresh all pivot tables at once. The macro is as follows.

Please Login or Register  to view this content.

Thanks

Thanks Erwin

I will try this and see if I can add the Conditional Formattting in as well.

Thanks again - this is a good improvement on the first answer. I have tried this though, and get an error.

Attachment 676125

The worksheet is not protected, so I am not sure why this does not work.

Here, hopefully is the image.

Attachment 676130

This did not work either - will try again (just noted the Advanced comment you made).

Here is the screenshot (3rd time lucky)

NumberFormat Error.png

I have attached a version with cleaned up data (I need to hide a few details). I hope I have successfully done this.

To test this if you click on the bottom row of the table, then Insert it will insert a row above. You can then fill in some details. Then on 'Pivots' Refresh the table.

Thanks for your help, and sorry for the newbie lack of knowledge on the forum.

Steve

Thanks rorya that works perfectly. The missing part in my code was the "DataRange".