CODEWITHSUNDEEP
excelvba
user3860954
user3860954

Reputation: 99

VBA Number format of Pivot-Table's data range doesn't work

Sub Cost()

    Dim CurrentPivot As String

    On Error GoTo OnError

    Expand

    CurrentPivot = ActiveSheet.Name

    ActiveSheet.PivotTables(CurrentPivot).PivotFields("Sum of Hours"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables(CurrentPivot).AddDataField ActiveSheet.PivotTables( _
        CurrentPivot).PivotFields("Cost"), "Sum of Cost", xlSum
    FormatProj
    Zero
    Range("B43:AJ5000").Select
    Selection.NumberFormat = "$#,##0.00;[Red]$#,##0.00"

OnError:
    MsgBox ("Viewing data in $Cost")

    ActiveWorkbook.ShowPivotTableFieldList = False
    Range("B44").Select

End Sub

My numbers still look like 2615233.698

Upvotes: 0

Views: 1634

Answers (2)

Andre Marchesi
Andre Marchesi

Reputation: 1

Define name PivotTable

Set tbl = wkstbl.PivotTables("ResumoEstados")
    
    With tbl
        teste = wks.Name & "!" & rngDinamica.CurrentRegion.Address
        .ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        teste, Version:=6)
        .RefreshTable
  'work with .databodyrange of the pivottable choice      
With .DataBodyRange
            .Cells.NumberFormat = "#,##0.00"
        End With
    End With

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33692

The code below witl format the PivotTable's range to the format you wanted.

Instead of using Range("B43:AJ5000").Select and later Selection, you can directly access the Pivot-Table's range and modify it's format with:

PvtTbl.TableRange2.NumberFormat = "$#,##0.00;[Red]$#,##0.00"

Code

Option Explicit

Sub Cost()

Dim CurrentPivot As String
Dim PvtTbl      As PivotTable

On Error GoTo OnError
'Expand ' <-- not sure what does this Sub-routine actually does

CurrentPivot = ActiveSheet.Name

' set the Pivot-Table object
Set PvtTbl = ActiveSheet.PivotTables(CurrentPivot)
With PvtTbl
    .PivotFields("Sum of Hours").Orientation = xlHidden
    .AddDataField .PivotFields("Cost"), "Sum of Cost", xlSum

    'FormatProj ' <-- not sure what does this Sub-routine actually does
    'Zero ' <-- not sure what does this Sub-routine actually does

    .TableRange2.NumberFormat = "$#,##0.00;[Red]$#,##0.00"
End With

OnError:
MsgBox "Viewing data in $Cost"

ActiveWorkbook.ShowPivotTableFieldList = False
Range("B44").Select

End Sub

Upvotes: 2

Related Questions