Attribute VB_Name = "Module2"
Option Explicit
Sub dd_TL_V3_2() 'Met en forme les données extraites de DD

' Désactive le rafaraichissement de l'écran
Application.ScreenUpdating = False

'Ajoute le nom de la feuille en "A1"
    Dim NomOnglet
    NomOnglet = Sheets(1).Name
    Range("A1") = NomOnglet

'Créé l'onglet 2
    Sheets.Add After:=Worksheets(Worksheets.Count)

'Créé la variable nom du nouvel onglet = "NewOnglet"
    Dim NewOnglet
     Sheets(2).Name = Worksheets(NomOnglet).Range("B5")
     NewOnglet = Sheets(2).Name
     Range("B6") = NewOnglet

'Copie le nom du run de la feuille 1 A1 en feuille 2 B2
Worksheets(NomOnglet).Range("A1").Copy Worksheets(NewOnglet).Range("B2")

'Met en forme le texte "MANIP" "PATIENT"
    With Range("A2:B6").Font
        .Size = 16
        .Name = "Calibri"
        .FontStyle = "Gras"
    End With

'Passe l'onglet en format paysage
Worksheets(NewOnglet).PageSetup.Orientation = xlLandscape

'Ajoute les couleur de fond du tableau "PATIENT"
    Worksheets(NewOnglet).Range("B2").Interior.color = RGB(255, 192, 0)
    Worksheets(NewOnglet).Range("B6").Interior.color = RGB(255, 255, 0)
    Worksheets(NewOnglet).Range("A10:G10").Interior.color = RGB(248, 203, 173)
    Worksheets(NewOnglet).Range("A11:G11").Interior.color = RGB(198, 224, 180)
    Worksheets(NewOnglet).Range("A12:G12").Interior.color = RGB(180, 198, 231)



'Ajoute les entêtes du tableau "PATIENT"
    Worksheets(NewOnglet).Range("A2").Value = "MANIP"
    Worksheets(NewOnglet).Range("A6").Value = "PATIENT"
    Worksheets(NewOnglet).Range("A9").Value = "Well"
    Worksheets(NewOnglet).Range("B9").Value = "Sample"
    Worksheets(NewOnglet).Range("C9").Value = "Target"
    Worksheets(NewOnglet).Range("D9").Value = "FractionalAbundance"
    Worksheets(NewOnglet).Range("E9").Value = "% Mutation"
    Worksheets(NewOnglet).Range("F9").Value = "Droplet M"
    Worksheets(NewOnglet).Range("G9").Value = "Droplet WT"
    Worksheets(NewOnglet).Range("A14").Value = "BLANC"
    Worksheets(NewOnglet).Range("A22").Value = "TEMOIN_POSITIF"

'Ajoute la mise en forme conditionnnelle texte rouge si VAF >0.1
    Range("A10:G12").Select
    Application.CutCopyMode = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$E10=""Echec"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).Interior.color = RGB(255, 50, 50)
    Selection.FormatConditions(1).Font.Bold = True
    With Selection.FormatConditions(1).Borders
        .LineStyle = xlContinuous
        .color = -16776961
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$E10>0,1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetLastPriority
    With Selection.FormatConditions(1).Font
        .color = -16777024
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$E10=""WT"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetLastPriority
    With Selection.FormatConditions(1).Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

'Copie le tableau "Patient" pour faire les tableaux "BLANC" et "TEMOINS POSITIF"
    With Range("A9:G12")
        .Borders.Weight = xlThin
        .Copy Range("A17:G17")
        .Copy Range("A25:G25")
    End With

'Place les temoin de leur tableau
Worksheets(NomOnglet).Range("A3:G3").Copy
Worksheets(NewOnglet).Range("A18").PasteSpecial Paste:=xlPasteValues
Worksheets(NomOnglet).Range("A67:G67").Copy
Worksheets(NewOnglet).Range("A19").PasteSpecial Paste:=xlPasteValues
Worksheets(NomOnglet).Range("A35:G35").Copy
Worksheets(NewOnglet).Range("A20").PasteSpecial Paste:=xlPasteValues
Worksheets(NomOnglet).Range("A4:G4").Copy
Worksheets(NewOnglet).Range("A26").PasteSpecial Paste:=xlPasteValues
Worksheets(NomOnglet).Range("A68:G68").Copy
Worksheets(NewOnglet).Range("A27").PasteSpecial Paste:=xlPasteValues
Worksheets(NomOnglet).Range("A36:G36").Copy
Worksheets(NewOnglet).Range("A28").PasteSpecial Paste:=xlPasteValues

'Met en forme le texte des entêtes des tableaux "PATIENT","BLANC" et "TEMOINS POSITIF"
    With Range("A9:G28")
        .Font.Size = 12
        .Font.Name = "Calibri"
        .Font.FontStyle = "Gras"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .EntireColumn.AutoFit
    End With

'Supprime la couleur des fond pour les tableaux "BLANC" et "TEMOINS POSITIF"
    Worksheets(NewOnglet).Range("A18:G20").Interior.color = xlNone
    Worksheets(NewOnglet).Range("A26:G28").Interior.color = xlNone
    Worksheets(NewOnglet).Range("A18:G28").FormatConditions.Delete

'Supprime la mise en gras du texte pour les tableaux "BLANC" et "TEMOINS POSITIF"
    Worksheets(NewOnglet).Range("A18:G20").Font.Bold = False
    Worksheets(NewOnglet).Range("A26:G28").Font.Bold = False


'Duplique l'onglet 2 29 fois (30 personnes)
Dim i As Integer
    For i = 1 To 29 'défini le nombre de duplications
        Sheets(2).Copy After:=Sheets(Sheets.Count) 'Duplique l'onget 2 et le met en dernière position
        ActiveSheet.Name = Worksheets(NomOnglet).Range("B" & 6 + i - 1) 'nomme le nouvel onglet avec la valeur de la case B6 (puis B7 puis B8...) de la feuille  1
        Range("B6") = ActiveSheet.Name 'met le nom de l'onglet en B6
    Next

'Transfert les données patients dans les onglets
Sheets(NomOnglet).Range("A5:G5").Copy
Sheets(2).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A37:G37").Copy
Sheets(2).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A69:G69").Copy
Sheets(2).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A6:G6").Copy
Sheets(3).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A38:G38").Copy
Sheets(3).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A70:G70").Copy
Sheets(3).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A7:G7").Copy
Sheets(4).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A39:G39").Copy
Sheets(4).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A71:G71").Copy
Sheets(4).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A8:G8").Copy
Sheets(5).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A40:G40").Copy
Sheets(5).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A72:G72").Copy
Sheets(5).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A9:G9").Copy
Sheets(6).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A41:G41").Copy
Sheets(6).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A73:G73").Copy
Sheets(6).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A10:G10").Copy
Sheets(7).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A42:G42").Copy
Sheets(7).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A74:G74").Copy
Sheets(7).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A11:G11").Copy
Sheets(8).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A43:G43").Copy
Sheets(8).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A75:G75").Copy
Sheets(8).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A12:G12").Copy
Sheets(9).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A44:G44").Copy
Sheets(9).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A76:G76").Copy
Sheets(9).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A13:G13").Copy
Sheets(10).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A45:G45").Copy
Sheets(10).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A77:G77").Copy
Sheets(10).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A14:G14").Copy
Sheets(11).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A46:G46").Copy
Sheets(11).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A78:G78").Copy
Sheets(11).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A15:G15").Copy
Sheets(12).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A47:G47").Copy
Sheets(12).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A79:G79").Copy
Sheets(12).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A16:G16").Copy
Sheets(13).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A48:G48").Copy
Sheets(13).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A80:G80").Copy
Sheets(13).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A17:G17").Copy
Sheets(14).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A49:G49").Copy
Sheets(14).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A81:G81").Copy
Sheets(14).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A18:G18").Copy
Sheets(15).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A50:G50").Copy
Sheets(15).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A82:G82").Copy
Sheets(15).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A19:G19").Copy
Sheets(16).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A51:G51").Copy
Sheets(16).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A83:G83").Copy
Sheets(16).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A20:G20").Copy
Sheets(17).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A52:G52").Copy
Sheets(17).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A84:G84").Copy
Sheets(17).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A21:G21").Copy
Sheets(18).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A53:G53").Copy
Sheets(18).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A85:G85").Copy
Sheets(18).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A22:G22").Copy
Sheets(19).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A54:G54").Copy
Sheets(19).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A86:G86").Copy
Sheets(19).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A23:G23").Copy
Sheets(20).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A55:G55").Copy
Sheets(20).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A87:G87").Copy
Sheets(20).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A24:G24").Copy
Sheets(21).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A56:G56").Copy
Sheets(21).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A88:G88").Copy
Sheets(21).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A25:G25").Copy
Sheets(22).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A57:G57").Copy
Sheets(22).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A89:G89").Copy
Sheets(22).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A26:G26").Copy
Sheets(23).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A58:G58").Copy
Sheets(23).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A90:G90").Copy
Sheets(23).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A27:G27").Copy
Sheets(24).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A59:G59").Copy
Sheets(24).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A91:G91").Copy
Sheets(24).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A28:G28").Copy
Sheets(25).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A60:G60").Copy
Sheets(25).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A92:G92").Copy
Sheets(25).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A29:G29").Copy
Sheets(26).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A61:G61").Copy
Sheets(26).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A93:G93").Copy
Sheets(26).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A30:G30").Copy
Sheets(27).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A62:G62").Copy
Sheets(27).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A94:G94").Copy
Sheets(27).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A31:G31").Copy
Sheets(28).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A63:G63").Copy
Sheets(28).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A95:G95").Copy
Sheets(28).Range("A11").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("G63").Copy
Sheets(28).Range("G11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A32:G32").Copy
Sheets(29).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A64:G64").Copy
Sheets(29).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A96:G96").Copy
Sheets(29).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A33:G33").Copy
Sheets(30).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A65:G65").Copy
Sheets(30).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A97:G97").Copy
Sheets(30).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

Sheets(NomOnglet).Range("A34:G34").Copy
Sheets(31).Range("A10").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A66:G66").Copy
Sheets(31).Range("A12").PasteSpecial Paste:=xlPasteValues
Sheets(NomOnglet).Range("A98:G98").Copy
Sheets(31).Range("A11").PasteSpecial Paste:=xlPasteValues
Worksheets(NewOnglet).Columns("A:G").EntireColumn.AutoFit

' Réactive le rafaraichissement de l'écran
Application.ScreenUpdating = True

End Sub