VBA, Format a Column

Mattador · Feb 1st 2017

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:

Code

Sub Copysheet_Detail()
    
    Call trigger_Pivotrefresh
    Call Drill_on_pivot_andsavesheet
    
    Dim MyDate As String
    Dim cc As Range
    
    MyDate = Format(Now, "mm-ss")
    '
    
    Set cc = Sheets("Line Item").Range("B10")
    


    Sheets("Line Item").Copy After:=Sheets("Line Item")
    Sheets("Line Item (2)").Name = cc & " Det " & MyDate




        With Sheets(cc & " Det " & MyDate)
    
            .Unprotect
            .Cells.Copy
            .Cells.PasteSpecial xlValues
            
            'To set to 2pd, not working???
            '.Columns("E:E").NumberFormat = "#,##0"
           
            '.Range("$AP$2:$AP$218").AutoFilter Field:=1, Criteria1:="Show"
        End With
        
       


        
        
Range("B10").Select
SendKeys "{ESC}", True
Sheets("CC REP").Activate


End Sub

Display More

Thanks in advance

**KjBox** · Feb 2nd 2017

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.

**Trebor76** · Feb 2nd 2017

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

VBA, Format a Column

Participate now!

Share