VBA, Format a Column

  • Hi,


    I have some code that will automatically create the drill down data on multiple pivot tables and extract the data to separate sheets.


    This is gonig to be put in a loop and done for multiple reports, hence not manually clicking into the drill down field.


    My question is as follows:


    I want to auto format each sheet as it is created so that the currency values in a specific column are formated to 2dp .i.e. '199,88.22'.


    I have tried the following code, but ih no effect

    Code
    .Columns("E:E").NumberFormat = "#,##0"

    .


    Can anyone correct this code?


    Full code below:




    Thanks in advance

  • Re: VBA, Format a Column


    Try

    Code
    .UsedRange.Columns(5).NumberFormat = "#,##0.00"


    Note that if you format the entire sheet column, not just the used range then you will massively bloat your file size.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: VBA, Format a Column


    Hi Mattador,


    Just add the decimal places to your existing code, i.e. try changing this line...


    Code
    .Columns("E:E").NumberFormat = "#,##0"


    ...to this:


    Code
    .Columns("E:E").NumberFormat = "#,##0.00"


    Don't forget the recorder for simple tasks like this as well.


    Regards,


    Robert

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!