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:
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
