Formatting Pivot Tables Version 3

I’ll try to tone down the hyperbole from the last post. It all started with a simple macro to format the selection with the comma style.

I loved this little macro. Then I decided that formatting pivot tables was just as tedious as applying styles. I made Version 2, and I saw that it was good.

The other day I was making a pivot table that used the Count aggregation. I don’t use Count anywhere near as much as Sum, but there I was. Showing two decimal places with Count isn’t the worst thing in the world, but I didn’t like it. Counting is done with whole numbers. Nor did I like changing the number format manually. Version 3 was born.

My initial revision checked whether the PivotField.Function property was xlSum or xlCount and applied formatting appropriately. That felt too rigid. Once my fingers are on Ctrl+M, it really doesn’t matter to me how many times I have to press down (within reason of course). So I went with a toggle between two decimals and no decimals. Now I’m happy again.

To assign to a shortcut key, I have these two statements in my Auto_Open and Auto_Close procedures, respectively.

One thought on “Formatting Pivot Tables Version 3

  1. My initial revision checked whether the PivotField.Function property was xlSum or xlCount and applied formatting appropriately. That felt too rigid. Once my fingers are on Ctrl+M, it really doesn’t matter to me how many times I have to press down (within reason of course).

    That’s hilarious. You love keyboard shortcuts so much, that your approach requires you to use two shortcuts instead of one.

    Yep, you’re a keyboard shortcut addict, alright!

    :-)


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *