'****************************************************************************************************
    '                                                                                                   *
    '                                   %Accessibilite W6 DPI                                           *
    '                                                                                                   *
    '****************************************************************************************************
Sub h_ACCESSIBILITE_W6_DPI()

   Worksheets("Fichier Global").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Fichier Global!R1C1:R16007C84", Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:="", TableName:= _
        "Tableau Accessibilite W6 DPI", DefaultVersion:=xlPivotTableVersion12
        
        
        On Error Resume Next
ActiveSheet.Name = "%Accessibilite W6 DPI"
Sheets("%Accessibilite W6 DPI").Move After:=Sheets("Com Encad sur ASO")

    With ActiveSheet.PivotTables("Tableau Accessibilite W6 DPI").PivotFields( _
        "NatOp")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Tableau Accessibilite W6 DPI").PivotFields( _
        "NatOp")
        .PivotItems("1").Visible = False
        .PivotItems("2").Visible = False
        .PivotItems("3").Visible = False
        
    End With
    With ActiveSheet.PivotTables("Tableau Accessibilite W6 DPI").PivotFields( _
        "NatOp")
        .PivotItems("Y9").Visible = False
        .PivotItems("Z1").Visible = False
        .PivotItems("Z6").Visible = False
        .PivotItems("Z8").Visible = False
    End With
    With ActiveSheet.PivotTables("Tableau Accessibilite W6 DPI").PivotFields("POLE" _
        )
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Tableau Accessibilite W6 DPI").PivotFields("Site" _
        )
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("Tableau Accessibilite W6 DPI").PivotFields("POLE" _
        )
        .PivotItems("CARHAIX").Visible = False
        .PivotItems("PONTIVY").Visible = False
    End With
    ActiveSheet.PivotTables("Tableau Accessibilite W6 DPI").AddDataField _
        ActiveSheet.PivotTables("Tableau Accessibilite W6 DPI").PivotFields("Client"), _
        "Somme de Client", xlSum
    With ActiveSheet.PivotTables("Tableau Accessibilite W6 DPI").PivotFields( _
        "Somme de Client")
        .Caption = "Nombre de Client"
        .Function = xlCount
    End With
    With ActiveSheet.PivotTables("Tableau Accessibilite W6 DPI").PivotFields( _
        "Accessibilité")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("Tableau Accessibilite W6 DPI").PivotFields( _
        "Accessibilité")
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.PivotTables("Tableau Accessibilite W6 DPI").TableStyle2 = _
    "Style de tableau croisé dynamique 1"
    Range("H21").Select
    
    
    
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Select
    ActiveSheet.Name = "annexe"
    Range("A1").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Fichier Global!R1C1:R1048576C80", Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:="annexe!R1C1", TableName:= _
        "Tableau croisé dynamique1", DefaultVersion:=xlPivotTableVersion12
    Sheets("annexe").Select
    Cells(1, 1).Select
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("POLE")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Site")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
        "Accessibilité")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique1").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique1").PivotFields("Client"), _
        "Nombre de Client", xlCount
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
        "Accessibilité")
        .PivotItems("(blank)").Visible = False
    End With
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Client")
        .PivotItems("(blank)").Visible = False
    End With
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("POLE")
        .PivotItems("CARHAIX").Visible = False
        .PivotItems("PONTIVY").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique1").TableStyle2 = _
        "Style de tableau croisé dynamique 1"
    ActiveSheet.PivotTables("Tableau croisé dynamique1").Name = _
        "Tableau annexe ASO"
         ActiveSheet.PivotTables("Tableau annexe ASO").PivotSelect "", _
        xlDataAndLabel, True
    Selection.Copy
    Sheets("%Accessibilite W6 DPI").Select
    Range("A30").Select
    ActiveSheet.Paste
    Sheets("annexe").Select
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    
Worksheets("%Accessibilite W6 DPI").Select

    Cells.Select
    Cells.EntireColumn.AutoFit
    
ActiveWorkbook.ShowPivotTableFieldList = False


' Colonne %Accessibilite
Range("E31").Select
ActiveCell.Value = "% Accessibilite"
ActiveCell.Offset(1, 0).Select
For Each cell In Range("D32:D" & Range("D" & Rows.Count).End(xlUp).Row)
With ActiveCell
.FormulaLocal = "= C" & ActiveCell.Row & "/D" & ActiveCell.Row & ""
.Style = "Percent"
.NumberFormat = "0.0%"
End With
ActiveCell.Offset(1, 0).Select
Next


'Création & remplissage & mise en forme du tableau annexe (%Accessibilite impayes, %accessibilite W6, %Accessibilite toutes NATOP)
Dim c As Range
Dim MaValeur As String

Worksheets("%Accessibilite W6 DPI").Select
Range("H2").Select
With ActiveCell
.Value = "% ACCESSIBILITE IMPAYES"
.Offset(0, 1).Value = "% ACCESSIBILITE W6"
.Offset(0, 2).Value = "% ACCESSIBILITE TOUTES NATOP"
.Offset(1, 0).Select
End With

For Each ligne In Range("F3:F" & Range("F" & Rows.Count).End(xlUp).Row)
MaValeur = Range("A" & ActiveCell.Row).Value
Set c = Sheets("Matrice").Range("B2:B25").Find(MaValeur, LookIn:=xlValues, lookat:=xlWhole)
Set D = Sheets("Matrice").Range("A2:A25").Find(MaValeur, LookIn:=xlValues, lookat:=xlWhole)

If MaValeur = "N" Or MaValeur = "O" Then
With ActiveCell
.Interior.Color = RGB(178, 178, 178)
.Offset(0, 1).Interior.Color = RGB(178, 178, 178)
.Offset(0, 2).Interior.Color = RGB(178, 178, 178)
End With



ElseIf MaValeur = "Total général" Then
With ActiveCell
.FormulaLocal = _
"=(D6+D9+D13+D20+D23+D16+E23+E20+E16+E13+E9+E6+B6+B23+B20+B16+B13+B9)/(D" & ActiveCell.Row & "+E" & ActiveCell.Row & "+B" & ActiveCell.Row & ")"

.Interior.Color = RGB(0, 176, 240)
.Style = "Percent"
.NumberFormat = "0.0%"

    With .Offset(0, 1)
    .FormulaLocal = _
"=(C6+C9+C13+C20+C23+C16)/(C" & ActiveCell.Row & ")"
    .Interior.Color = RGB(0, 176, 240)
    .Style = "Percent"
    .NumberFormat = "0.0%"
    End With

    With .Offset(0, 2)
    .FormulaLocal = "=E41"
    .Interior.Color = RGB(0, 176, 240)
    .Style = "Percent"
    .NumberFormat = "0.0%"
    End With
End With

ElseIf Not D Is Nothing Then
With ActiveCell
.FormulaLocal = _
"= (D" & ActiveCell.Offset(2, 0).Row & "+E" & ActiveCell.Offset(2, 0).Row & "+B" & ActiveCell.Offset(2, 0).Row & ")" _
& "/(D" & ActiveCell.Row & "+E" & ActiveCell.Row & "+B" & ActiveCell.Row & ")"
    With .Offset(0, 1)
    .FormulaLocal = _
    "=C" & ActiveCell.Offset(2, 0).Row & "/C" & ActiveCell.Row & ""
    .Style = "Percent"
    .NumberFormat = "0.0%"
    End With
    
    With .Offset(0, 2)
    .FormulaLocal = "=RECHERCHEV(A" & ActiveCell.Row & " ;$A$31:$E$41;5;FAUX)"
    .Style = "Percent"
    .NumberFormat = "0.0%"
    End With
.Style = "Percent"
.NumberFormat = "0.0%"
End With

ElseIf Not c Is Nothing Then
With ActiveCell
.FormulaLocal = _
"= (D" & ActiveCell.Offset(3, 0).Row & "+D" & ActiveCell.Offset(6, 0).Row & "+E" & ActiveCell.Offset(3, 0).Row & " +E" & ActiveCell.Offset(6, 0).Row & "+B" & ActiveCell.Offset(3, 0).Row & "+B" & ActiveCell.Offset(6, 0).Row & ")" _
& "/(D" & ActiveCell.Row & "+E" & ActiveCell.Row & "+B" & ActiveCell.Row & ")"

    With .Offset(0, 1)
    .FormulaLocal = _
"=(C" & ActiveCell.Offset(3, 0).Row & "+C" & ActiveCell.Offset(6, 0).Row & ")/C" & ActiveCell.Row & ""
    .Style = "Percent"
    .NumberFormat = "0.0%"
    .Interior.Color = RGB(255, 255, 0)
    End With

    With .Offset(0, 2)
    .FormulaLocal = "=RECHERCHEV(A" & ActiveCell.Row & " ;$A$31:$E$41;5;FAUX)"
    .Style = "Percent"
    .NumberFormat = "0.0%"
    .Interior.Color = RGB(255, 255, 0)
    End With
.Interior.Color = RGB(255, 255, 0)
.Style = "Percent"
.NumberFormat = "0.0%"
End With

End If
ActiveCell.Offset(1, 0).Select
Next

Set sh = ActiveSheet.Shapes.AddShape(msoShapeActionButtonReturn, 75, 0, 30, 30)
sh.Select
With Selection
.ShapeRange.Fill.ForeColor.SchemeColor = 1
End With
ActiveSheet.Hyperlinks.Add Anchor:=sh, Address:="", SubAddress:="Menu!A1"
End Sub