Microsoft 365 GRAPHIQUE VBA - COMMENT INDIQUER UNE VALEUR A ZERO SUR LE GRAPHIQUE

Charles78

XLDnaute Nouveau
Bonjour à tous,

J'ai ouvert une discussion https://www.excel-downloads.com/threads/creation-de-plusieurs-tcd-vba.20038112/page-2#post-20273213 par rapport à la création de plusieurs TCDs et la création de deux graphiques qui a été résolu. En effet, j'ai finalement créé 4 TCDs manuellement qui sont mis à jour en modifiant les fichiers sources et après actualisation des données.
Ma question porte sur le graphique en histogramme qui est situé sur l'onglet "DASHBOARD" lié au TCD de l'onglet "TCD RUPTURE RATE" dont le fichier source est situé dans l'onglet "NDR".Ce fichier source est modifié par macro avec la suppression; le rajout de colonnes ainsi que la suppression de lignes en fonction d'un filtre. Il se peut que pour une semaine donnée qu'au final aucune ligne ne sera présente dans l'onglet "NDR" (il n'y aura que les en-têtes). Avec la macro actuelle concernant la création du graphique, si je n'ai rien sur le fichier source et par conséquent rien dans le TCD, il y a un message d'erreur. Au cas où le fichier source est vide, je souhaite tout de même indiquer pour mes 3 valeurs dont OOS (EUR), OOS (CON) et Number of SKUs qui sont sur le graphique le résultat 0.

Ci-dessous mon code que vous n'avez qu'à exécuter pour voir le résultat du graphique mais lorsque il y a des données dans le fichier source ainsi que mon fichier en pièce jointe:

VB:
Sub KPI_COPACKING()
    '
    ' KPI_COPACKING Macro
    '

    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    
    
    ' CREATION TCD
    Dim objSheetWithData As Worksheet
    Dim objSheetWithPivot As Worksheet
    Dim objListObjectWithData As ListObject
    Dim objConnection As WorkbookConnection
    Dim objPivotCache As PivotCache
    Dim objPivotTable As PivotTable
    Dim objCubeField As CubeField
    Dim objPivotField As PivotField
    
    
    ' CREATION TCD LIVRAISON
    Set objSheetWithData = ActiveWorkbook.Sheets("LIVRAISON")
    Set objSheetWithPivot = ActiveWorkbook.Sheets("TCD SERVICE LEVEL")
    If objSheetWithData.ListObjects.Count > 0 Then
        Set objListObjectWithData = objSheetWithData.ListObjects(1)
    Else
        Set objListObjectWithData = objSheetWithData.ListObjects.Add( _
                                    SourceType:=xlSrcRange, _
                                    Source:=objSheetWithData.Range("LIVRAISON"), _
                                    XlListObjectHasHeaders:=xlYes)
    End If
    For Each objConnection In ActiveWorkbook.Connections
        If objConnection.Type = xlConnectionTypeWORKSHEET Then objConnection.Delete
    Next objConnection
    Set objConnection = ActiveWorkbook.Connections.Add2( _
                        Name:="LIVRAISON", _
                        Description:="TCD SERVICE LEVEL", _
                        ConnectionString:="WORKSHEET;" & ActiveWorkbook.Name, _
                        CommandText:=objListObjectWithData.Parent.Name & "!" & objListObjectWithData.Name, _
                        lCmdtype:=XlCmdType.xlCmdExcel, _
                        CreateModelConnection:=True, _
                        ImportRelationships:=False)
    Set objPivotCache = ActiveWorkbook.PivotCaches.Create( _
                        SourceType:=xlExternal, _
                        SourceData:=objConnection)
    With objPivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsNone
    End With
    For Each objPivotTable In objSheetWithPivot.PivotTables
        objPivotTable.TableRange2.Clear
    Next objPivotTable
    Set objPivotTable = objPivotCache.CreatePivotTable( _
                        TableDestination:=objSheetWithPivot.Range("A1"), TableName:="TCD LIVRAISON")
                        
    ' FILTRES
    
    ' IDH + DESIGNATION
    With objPivotTable.CubeFields(7)
        .Orientation = xlPageField
        .Caption = "IDH + Designation"
    End With
    objPivotTable.PageFields(1).Caption = "IDH + Designation"
    
    ' BRAND
    With objPivotTable.CubeFields(9)
        .Orientation = xlPageField
        .Caption = "Brand"
    End With
    objPivotTable.PageFields(2).Caption = "Brand"
    
    ' MARKET
    With objPivotTable.CubeFields(11)
        .Orientation = xlPageField
        .Caption = "Market"
    End With
    objPivotTable.PageFields(3).Caption = "Market"
    
    ' TYPE OF PRODUCT
    With objPivotTable.CubeFields(8)
        .Orientation = xlPageField
        .Caption = "Type of Product"
    End With
    objPivotTable.PageFields(4).Caption = "Type of Product"
    
    ' BUSINESS UNIT
    With objPivotTable.CubeFields(10)
        .Orientation = xlPageField
        .Caption = "Business Unit"
    End With
    objPivotTable.PageFields(5).Caption = "Business Unit"
    
    ' 100% ?
    With objPivotTable.CubeFields(15)
        .Orientation = xlPageField
        .Caption = "100% ?"
    End With
    objPivotTable.PageFields(6).Caption = "100% ?"
    
    ' VALEURS
    
    ' SERVICE LEVEL
    Set objCubeField = objPivotTable.CubeFields.GetMeasure( _
                       AttributeHierarchy:=objPivotTable.CubeFields(14), _
                       Function:=xlAverage, _
                       Caption:="Service Rate")
    objPivotTable.AddDataField objCubeField
    objPivotTable.DataFields(1).Caption = "Service Level (%)"
    objPivotTable.DataFields(1).NumberFormat = "0.00%"
    
    ' QUANTITY DELIVERED (PAL)
    Set objCubeField = objPivotTable.CubeFields.GetMeasure( _
                       AttributeHierarchy:=objPivotTable.CubeFields(13), _
                       Function:=xlSum, _
                       Caption:="Quantity delivered")
    objPivotTable.AddDataField objCubeField
    objPivotTable.DataFields(2).Caption = "Quantity Delivered (PAL)"
    objPivotTable.DataFields(2).NumberFormat = "#,##0.00"
    
    ' NUMBER OF SKUs
    Set objCubeField = objPivotTable.CubeFields.GetMeasure( _
                       AttributeHierarchy:=objPivotTable.CubeFields(7), _
                       Function:=xlDistinctCount, _
                       Caption:="IDH + Designation")
    objPivotTable.AddDataField objCubeField
    objPivotTable.DataFields(3).Caption = "Number of SKUs"
    objPivotTable.DataFields(3).NumberFormat = "#,##0"


    ' CREATION TCD RUPTURE RATE
    Set objSheetWithData = ActiveWorkbook.Sheets("NDR")
    Set objSheetWithPivot = ActiveWorkbook.Sheets("TCD RUPTURE RATE")
    If objSheetWithData.ListObjects.Count > 0 Then
        Set objListObjectWithData = objSheetWithData.ListObjects(1)
    Else
        Set objListObjectWithData = objSheetWithData.ListObjects.Add( _
                                    SourceType:=xlSrcRange, _
                                    Source:=objSheetWithData.Range("NDR"), _
                                    XlListObjectHasHeaders:=xlYes)
    End If
    For Each objConnection In ActiveWorkbook.Connections
        If objConnection.Type = xlConnectionTypeWORKSHEET Then objConnection.Delete
    Next objConnection
    Set objConnection = ActiveWorkbook.Connections.Add2( _
                        Name:="NDR", _
                        Description:="TCD RUPTURE RATE", _
                        ConnectionString:="WORKSHEET;" & ActiveWorkbook.Name, _
                        CommandText:=objListObjectWithData.Parent.Name & "!" & objListObjectWithData.Name, _
                        lCmdtype:=XlCmdType.xlCmdExcel, _
                        CreateModelConnection:=True, _
                        ImportRelationships:=False)
    Set objPivotCache = ActiveWorkbook.PivotCaches.Create( _
                        SourceType:=xlExternal, _
                        SourceData:=objConnection)
    With objPivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsNone
    End With
    For Each objPivotTable In objSheetWithPivot.PivotTables
        objPivotTable.TableRange2.Clear
    Next objPivotTable
    Set objPivotTable = objPivotCache.CreatePivotTable( _
                        TableDestination:=objSheetWithPivot.Range("A1"), TableName:="TCD NDR")
    
    ' FILTRES
    
    ' IDH + DESIGNATION
    With objPivotTable.CubeFields(6)
        .Orientation = xlPageField
        .Caption = "IDH + Designation"
    End With
    objPivotTable.PageFields(1).Caption = "IDH + Designation"
    
    ' BRAND
    With objPivotTable.CubeFields(8)
        .Orientation = xlPageField
        .Caption = "Brand"
    End With
    objPivotTable.PageFields(2).Caption = "Brand"
    
    ' MARKET
    With objPivotTable.CubeFields(10)
        .Orientation = xlPageField
        .Caption = "Market"
    End With
    objPivotTable.PageFields(3).Caption = "Market"
    
    ' TYPE OF PRODUCT
    With objPivotTable.CubeFields(7)
        .Orientation = xlPageField
        .Caption = "Type of Product"
    End With
    objPivotTable.PageFields(4).Caption = "Type of Product"
    
    ' BUSINESS UNIT
    With objPivotTable.CubeFields(9)
        .Orientation = xlPageField
        .Caption = "Business Unit"
    End With
    objPivotTable.PageFields(5).Caption = "Business Unit"
    
    ' VALEURS
    
    ' OOS (EUR)
    
    Set objCubeField = objPivotTable.CubeFields.GetMeasure( _
                       AttributeHierarchy:=objPivotTable.CubeFields(5), _
                       Function:=xlSum, _
                       Caption:="CPV (OOS) [EUR]")
    objPivotTable.AddDataField objCubeField
    objPivotTable.DataFields(1).Caption = "OOS (EUR)"
    objPivotTable.DataFields(1).NumberFormat = "#,##0.00 €"
    
    ' OOS (CON)
    Set objCubeField = objPivotTable.CubeFields.GetMeasure( _
                       AttributeHierarchy:=objPivotTable.CubeFields(4), _
                       Function:=xlSum, _
                       Caption:="(OOS) [CON]")
    objPivotTable.AddDataField objCubeField
    objPivotTable.DataFields(2).Caption = "OOS (CON)"
    objPivotTable.DataFields(2).NumberFormat = "#,##0"
    
    ' NUMBER OF SKUs
    Set objCubeField = objPivotTable.CubeFields.GetMeasure( _
                       AttributeHierarchy:=objPivotTable.CubeFields(6), _
                       Function:=xlDistinctCount, _
                       Caption:="IDH + Designation")
    objPivotTable.AddDataField objCubeField
    objPivotTable.DataFields(3).Caption = "Number of SKUs"
    objPivotTable.DataFields(3).NumberFormat = "#,##0"
 
      
    ' CREATION TCD VALUE AND VOLUME
    Set objSheetWithData = ActiveWorkbook.Sheets("PRODUCTION")
    Set objSheetWithPivot = ActiveWorkbook.Sheets("TCD VALUE AND VOLUME")
    If objSheetWithData.ListObjects.Count > 0 Then
        Set objListObjectWithData = objSheetWithData.ListObjects(1)
    Else
        Set objListObjectWithData = objSheetWithData.ListObjects.Add( _
                                    SourceType:=xlSrcRange, _
                                    Source:=objSheetWithData.Range("PRODUCTION"), _
                                    XlListObjectHasHeaders:=xlYes)
    End If
    For Each objConnection In ActiveWorkbook.Connections
        If objConnection.Type = xlConnectionTypeWORKSHEET Then objConnection.Delete
    Next objConnection
    Set objConnection = ActiveWorkbook.Connections.Add2( _
                        Name:="PRODUCTION", _
                        Description:="TCD VALUE AND VOLUME", _
                        ConnectionString:="WORKSHEET;" & ActiveWorkbook.Name, _
                        CommandText:=objListObjectWithData.Parent.Name & "!" & objListObjectWithData.Name, _
                        lCmdtype:=XlCmdType.xlCmdExcel, _
                        CreateModelConnection:=True, _
                        ImportRelationships:=False)
    Set objPivotCache = ActiveWorkbook.PivotCaches.Create( _
                        SourceType:=xlExternal, _
                        SourceData:=objConnection)
    With objPivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsNone
    End With
    For Each objPivotTable In objSheetWithPivot.PivotTables
        objPivotTable.TableRange2.Clear
    Next objPivotTable
    Set objPivotTable = objPivotCache.CreatePivotTable( _
                        TableDestination:=objSheetWithPivot.Range("A1"), TableName:="TCD PRODUCTION")

    ' FILTRES
    
    ' IDH + DESIGNATION
    With objPivotTable.CubeFields(9)
        .Orientation = xlPageField
        .Caption = "IDH + Designation"
    End With
    objPivotTable.PageFields(1).Caption = "IDH + Designation"
    
    ' BRAND
    With objPivotTable.CubeFields(11)
        .Orientation = xlPageField
        .Caption = "Brand"
    End With
    objPivotTable.PageFields(2).Caption = "Brand"
    
    ' MARKET
    With objPivotTable.CubeFields(13)
        .Orientation = xlPageField
        .Caption = "Market"
    End With
    objPivotTable.PageFields(3).Caption = "Market"
    
    ' TYPE OF PRODUCT
    With objPivotTable.CubeFields(10)
        .Orientation = xlPageField
        .Caption = "Type of Product"
    End With
    objPivotTable.PageFields(4).Caption = "Type of Product"
    
    ' BUSINESS UNIT
    With objPivotTable.CubeFields(12)
        .Orientation = xlPageField
        .Caption = "Business Unit"
    End With
    objPivotTable.PageFields(5).Caption = "Business Unit"
    
    ' VALEURS
    
    ' STOCK VALUE (EUR)
    Set objCubeField = objPivotTable.CubeFields.GetMeasure( _
                       AttributeHierarchy:=objPivotTable.CubeFields(5), _
                       Function:=xlSum, _
                       Caption:="Stock Value")
    objPivotTable.AddDataField objCubeField
    objPivotTable.DataFields(1).Caption = "Stock Value (EUR)"
    objPivotTable.DataFields(1).NumberFormat = "#,##0.00 €"
    
    ' QUANTITY PRODUCED (PAL)
    Set objCubeField = objPivotTable.CubeFields.GetMeasure( _
                       AttributeHierarchy:=objPivotTable.CubeFields(15), _
                       Function:=xlSum, _
                       Caption:="Amount of PAL")
    objPivotTable.AddDataField objCubeField
    objPivotTable.DataFields(2).Caption = "Quantity Produced (PAL)"
    objPivotTable.DataFields(2).NumberFormat = "#,##0.00"
    
    ' NUMBER OF SKUs
    Set objCubeField = objPivotTable.CubeFields.GetMeasure( _
                       AttributeHierarchy:=objPivotTable.CubeFields(9), _
                       Function:=xlDistinctCount, _
                       Caption:="IDH + Designation")
    objPivotTable.AddDataField objCubeField
    objPivotTable.DataFields(3).Caption = "Number of SKUs"
    objPivotTable.DataFields(3).NumberFormat = "#,##0"


    ' CREATION TCD OP REGIE
    Set objSheetWithData = ActiveWorkbook.Sheets("OP REGIE")
    Set objSheetWithPivot = ActiveWorkbook.Sheets("TCD VALUE AND VOLUME OP REGIE")
    If objSheetWithData.ListObjects.Count > 0 Then
        Set objListObjectWithData = objSheetWithData.ListObjects(1)
    Else
        Set objListObjectWithData = objSheetWithData.ListObjects.Add( _
                                    SourceType:=xlSrcRange, _
                                    Source:=objSheetWithData.Range("OP_REGIE"), _
                                    XlListObjectHasHeaders:=xlYes)
    End If
    For Each objConnection In ActiveWorkbook.Connections
        If objConnection.Type = xlConnectionTypeWORKSHEET Then objConnection.Delete
    Next objConnection
    Set objConnection = ActiveWorkbook.Connections.Add2( _
                        Name:="OP REGIE", _
                        Description:="TCD VALUE AND VOLUME OP REGIE", _
                        ConnectionString:="WORKSHEET;" & ActiveWorkbook.Name, _
                        CommandText:=objListObjectWithData.Parent.Name & "!" & objListObjectWithData.Name, _
                        lCmdtype:=XlCmdType.xlCmdExcel, _
                        CreateModelConnection:=True, _
                        ImportRelationships:=False)
    Set objPivotCache = ActiveWorkbook.PivotCaches.Create( _
                        SourceType:=xlExternal, _
                        SourceData:=objConnection)
    With objPivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsNone
    End With
    For Each objPivotTable In objSheetWithPivot.PivotTables
        objPivotTable.TableRange2.Clear
    Next objPivotTable
    Set objPivotTable = objPivotCache.CreatePivotTable( _
                        TableDestination:=objSheetWithPivot.Range("A1"), TableName:="TCD OP REGIE")

    ' FILTRES
    
    ' IDH + DESIGNATION
    With objPivotTable.CubeFields(12)
        .Orientation = xlPageField
        .Caption = "IDH + Designation"
    End With
    objPivotTable.PageFields(1).Caption = "IDH + Designation"
    
    ' BRAND
    With objPivotTable.CubeFields(13)
        .Orientation = xlPageField
        .Caption = "Brand"
    End With
    objPivotTable.PageFields(2).Caption = "Brand"
    
    ' MARKET
    With objPivotTable.CubeFields(14)
        .Orientation = xlPageField
        .Caption = "Market"
    End With
    objPivotTable.PageFields(3).Caption = "Market"
    
    ' TYPE OF PRODUCT
    With objPivotTable.CubeFields(15)
        .Orientation = xlPageField
        .Caption = "Type of Product"
    End With
    objPivotTable.PageFields(4).Caption = "Type of Product"
    
    ' BUSINESS UNIT
    With objPivotTable.CubeFields(16)
        .Orientation = xlPageField
        .Caption = "Business Unit"
    End With
    objPivotTable.PageFields(5).Caption = "Business Unit"
    
    ' VALEURS
    
    ' STOCK VALUE (EUR)
    Set objCubeField = objPivotTable.CubeFields.GetMeasure( _
                       AttributeHierarchy:=objPivotTable.CubeFields(20), _
                       Function:=xlSum, _
                       Caption:="Stock Value")
    objPivotTable.AddDataField objCubeField
    objPivotTable.DataFields(1).Caption = "Stock Value (EUR)"
    objPivotTable.DataFields(1).NumberFormat = "#,##0.00 €"
    
    ' QUANTITY PRODUCED (PAL)
    Set objCubeField = objPivotTable.CubeFields.GetMeasure( _
                       AttributeHierarchy:=objPivotTable.CubeFields(17), _
                       Function:=xlSum, _
                       Caption:="Quantity (PAL)")
    objPivotTable.AddDataField objCubeField
    objPivotTable.DataFields(2).Caption = "Quantity Produced (PAL)"
    objPivotTable.DataFields(2).NumberFormat = "#,##0.00"
    
    ' NUMBER OF SKUs
    Set objCubeField = objPivotTable.CubeFields.GetMeasure( _
                       AttributeHierarchy:=objPivotTable.CubeFields(12), _
                       Function:=xlDistinctCount, _
                       Caption:="IDH + Designation")
    objPivotTable.AddDataField objCubeField
    objPivotTable.DataFields(3).Caption = "Number of SKUs"
    
    
    
    ' TABLE TCD


    ' TABLE TCD LIVRAISON
    Sheets("TCD SERVICE LEVEL").Select
    Range("A12").Select
    ActiveCell.FormulaR1C1 = "Mauvais"
    Range("A13").Select
    ActiveCell.FormulaR1C1 = "Moyen"
    Range("A14").Select
    ActiveCell.FormulaR1C1 = "Bon"
    Range("A15").Select
    ActiveCell.FormulaR1C1 = "Vide"
    Range("A17").Select
    ActiveCell.FormulaR1C1 = "Valeur"
    Range("A18").Select
    ActiveCell.FormulaR1C1 = "Aiguille"
    Range("A19").Select
    ActiveCell.FormulaR1C1 = "Vide"
    Range("A21").Select
    ActiveCell.FormulaR1C1 = "Quantity Delivered (PAL):"
    Range("A22").Select
    ActiveCell.FormulaR1C1 = "Number of SKUs:"
    Range("A23").Select
    ActiveCell.FormulaR1C1 = "IDH + Designation"
    Range("B12").Select
    ActiveCell.FormulaR1C1 = "0.8"
    Range("B13").Select
    ActiveCell.FormulaR1C1 = "0.11"
    Range("B14").Select
    ActiveCell.FormulaR1C1 = "0.1"
    Range("B15").Select
    ActiveCell.FormulaR1C1 = "0.99"
    Range("B12:B15").Select
    Selection.Style = "Percent"
    Range("A12:B15").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("B17").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
                           "=GETPIVOTDATA(""[Measures].[Moyenne de Service Rate]"",R8C1)"
    Range("B17").Select
    Selection.NumberFormat = "0.00%"
    Range("B18").Select
    Selection.Style = "Percent"
    With Selection.Font
        .Name = "Calibri"
        .FontStyle = "Normal"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveCell.FormulaR1C1 = "2%"
    Range("B19").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-8]C:R[-4]C)-(R[-1]C+R[-2]C)"
    Range("A17:B19").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("B21").Select
    ActiveCell.FormulaR1C1 = _
                           "=CONCATENATE(RC[-1],""     "",(TEXT(ROUND(GETPIVOTDATA(""[Measures].[Somme de Quantity delivered]"",R8C1),2),""# ##0,00"")))"
    Range("B22").Select
    ActiveCell.FormulaR1C1 = _
                           "=IF(R[-21]C<>""All"","" "",CONCATENATE(RC[-1],""     "",GETPIVOTDATA(""[Measures].[Total distinct de IDH + Designation]"",R8C1)))"
    Range("B23").Select
    ActiveCell.FormulaR1C1 = _
                           "=IF(R[-22]C<>""All"",R[-22]C,"" "")"
    Range("A21:B23").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit


    ' TABLE TCD PRODUCTION
    Sheets("TCD VALUE AND VOLUME").Select
    Range("A12").Select
    ActiveCell.FormulaR1C1 = "Stock Value:"
    Range("A13").Select
    ActiveCell.FormulaR1C1 = "Quantity Produced (PAL):"
    Range("A14").Select
    ActiveCell.FormulaR1C1 = "Number of SKUs:"
    Range("A15").Select
    ActiveCell.FormulaR1C1 = "IDH + Designation"
    Range("B12").Select
    ActiveCell.FormulaR1C1 = _
                           "=CONCATENATE(RC[-1],""     "",""€"","" "",(TEXT(ROUND(GETPIVOTDATA(""[Measures].[Somme de Stock Value]"",R7C1),2),""# ##0,00"")))"
    Range("B13").Select
    ActiveCell.FormulaR1C1 = _
                           "=CONCATENATE(RC[-1],""     "",(TEXT(ROUND(GETPIVOTDATA(""[Measures].[Somme de Amount of PAL]"",R7C1),2),""# ##0,00"")))"
    Range("B14").Select
    ActiveCell.FormulaR1C1 = _
                           "=IF(R[-13]C<>""All"","" "",CONCATENATE(RC[-1],""     "",GETPIVOTDATA(""[Measures].[Total distinct de IDH + Designation]"",R7C1)))"
    Range("B15").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-14]C<>""All"",R[-14]C,"" "")"


    ' TABLE TCD OP REGIE
    Sheets("TCD VALUE AND VOLUME OP REGIE").Select
    Range("A12").Select
    ActiveCell.FormulaR1C1 = "Stock Value:"
    Range("A13").Select
    ActiveCell.FormulaR1C1 = "Quantity Produced (PAL):"
    Range("A14").Select
    ActiveCell.FormulaR1C1 = "Number of SKUs:"
    Range("A15").Select
    ActiveCell.FormulaR1C1 = "IDH + Designation"
    Range("B12").Select
    ActiveCell.FormulaR1C1 = _
                           "=CONCATENATE(RC[-1],""     "",""€"","" "",(TEXT(ROUND(GETPIVOTDATA(""[Measures].[Somme de Stock Value]"",R7C1),2),""# ##0,00"")))"
    Range("B13").Select
    ActiveCell.FormulaR1C1 = _
                           "=CONCATENATE(RC[-1],""     "",(TEXT(ROUND(GETPIVOTDATA(""[Measures].[Somme de Quantity (PAL)]"",R7C1),2),""# ##0,00"")))"
    Range("B14").Select
    ActiveCell.FormulaR1C1 = _
                           "=IF(R[-13]C<>""All"","" "",CONCATENATE(RC[-1],""     "",GETPIVOTDATA(""[Measures].[Total distinct de IDH + Designation]"",R7C1)))"
    Range("B15").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-14]C<>""All"",R[-14]C,"" "")"

     ' CREATION GRAPHIQUE NDR
    
    Dim wksPivot As Worksheet
    Dim wksDest As Worksheet
    Dim oChart As Chart
    Dim oPT As PivotTable
    Dim rDest As Range
    
    Set wksPivot = Worksheets("TCD RUPTURE RATE")
    Set wksDest = Worksheets("DASHBOARD")
    
    Set oPT = wksPivot.PivotTables("TCD NDR")
    
    Set rDest = wksDest.Range("B113")
    
    With rDest
        Set oChart = wksDest.ChartObjects.Add(Left:=.Left, Top:=.Top, Width:=400, Height:=255).Chart
    End With
    
    With oChart
        .ChartType = xlColumnClustered
        .SetSourceData oPT.TableRange1
        .ApplyLayout (4)
        .Parent.Name = "Graphique NDR"
        .ShowAllFieldButtons = False
        .Axes(xlCategory).Delete
        .Axes(xlValue).Delete
        .HasTitle = True
        .ChartTitle.Characters.Text = "RUPTURE RATE"
        .ChartTitle.Font.Bold = True
        .ChartTitle.Font.Size = 24
    End With
    wksDest.Activate
    Range("B113").Select
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic

End Sub
 

Pièces jointes

  • FICHIER CREATION TCD V2.xlsm
    62.6 KB · Affichages: 14

Charles78

XLDnaute Nouveau
Bonjour,

Si je suis sur la bonne erreur teste d'abord :
VB:
if objPivotTable.DataFields.Count =0 then
    ' ...
End if
Soit tu ne fais pas la suite, soit tu en profites pour aller mettre une ligne dans les données
eric
Bonsoir Eric,

J'ai opté pour la solution de rajouter sur le fichier source la valeur 0 pour les 3 colonnes concernés par les data fileds dans la liste de champs et ça fonctionne après avoir ajuster la macro comme par exemple pour le nombre de référence au lieu de mettre la fonction Total Distinct je l'ai modifié en fonction Somme afin de voir 0 sur le graphique et non 1.

Merci.
 

Charles78

XLDnaute Nouveau
Bonsoir Eric,

J'ai opté pour la solution de rajouter sur le fichier source la valeur 0 pour les 3 colonnes concernés par les data fileds dans la liste de champs et ça fonctionne après avoir ajuster la macro comme par exemple pour le nombre de référence au lieu de mettre la fonction Total Distinct je l'ai modifié en fonction Somme afin de voir 0 sur le graphique et non 1.

Merci.
J'ai oublié de poser une autre question. Comment peut-on procéder pour "fusionner" plusieurs fichiers sources en un fichier source en VBA afin ensuite de créer un autre TCD ?
 

eriiic

XLDnaute Barbatruc
Bonjour,

A l'ancienne : ajoute à la barre d'outil l'Assistant tableau croisé dynamique.

Ou plus souple et puissant : regarde du coté de Power pivot.
Présent sur les versions récentes, il faut le télécharger pour l'ajouter aux plus anciennes versions d'excel.
Je ne pratique pas suffisamment pour te guider.
eric
 

Statistiques des forums

Discussions
311 720
Messages
2 081 925
Membres
101 841
dernier inscrit
ferid87