XL 2013 Macro de recopie sans copier les formules juste les valeurs et ajouter conditions

jay72000 · 18 Juin 2020

Bonjour amis excellent,

J'aurais besoin d'une aide, j'utilise une macro de recopie.
La macro copie de la feuille MRE vers la feuille sauvegarde.
Elle incrémente une liste.
Mon premier problème, comment faire pour que la macro copie la valeur des cases, non pas la formule qui va avec ?
Mon second probleme : Comment faire pour rajouter des conditions : Si il y a ** dans les cases B7 F7 et F8 de la feuille MRE ne pas lancer la macro afficher le message d'erreur cases non remplis.

Merci

Code :

VB:

Sub recopie()
    Dim ligVide As Long
        ligVide = Sheets("sauvegarde").Range("D" & Rows.Count).End(xlUp).Row + 1
    Worksheets("MRE").Range("G3").Copy (Worksheets("sauvegarde").Cells(ligVide, 1))
    Worksheets("MRE").Range("B8").Copy (Worksheets("sauvegarde").Cells(ligVide, 2))
    Worksheets("MRE").Range("F8").Copy (Worksheets("sauvegarde").Cells(ligVide, 3))
    Worksheets("MRE").Range("A12").Copy (Worksheets("sauvegarde").Cells(ligVide, 4))
    Worksheets("MRE").Range("B12").Copy (Worksheets("sauvegarde").Cells(ligVide, 5))
    Worksheets("MRE").Range("F7").Copy (Worksheets("sauvegarde").Cells(ligVide, 6))
    Worksheets("MRE").Range("D12").Copy (Worksheets("sauvegarde").Cells(ligVide, 7))
    Worksheets("MRE").Range("E12").Copy (Worksheets("sauvegarde").Cells(ligVide, 8))
    Worksheets("MRE").Range("F12").Copy (Worksheets("sauvegarde").Cells(ligVide, 9))
    Worksheets("MRE").Range("G12").Copy (Worksheets("sauvegarde").Cells(ligVide, 10))
        ligVide = Sheets("sauvegarde").Range("D" & Rows.Count).End(xlUp).Row + 1
    Worksheets("MRE").Range("G3").Copy (Worksheets("sauvegarde").Cells(ligVide, 1))
    Worksheets("MRE").Range("B8").Copy (Worksheets("sauvegarde").Cells(ligVide, 2))
    Worksheets("MRE").Range("F8").Copy (Worksheets("sauvegarde").Cells(ligVide, 3))
    Worksheets("MRE").Range("A13").Copy (Worksheets("sauvegarde").Cells(ligVide, 4))
    Worksheets("MRE").Range("B13").Copy (Worksheets("sauvegarde").Cells(ligVide, 5))
    Worksheets("MRE").Range("F7").Copy (Worksheets("sauvegarde").Cells(ligVide, 6))
    Worksheets("MRE").Range("D13").Copy (Worksheets("sauvegarde").Cells(ligVide, 7))
    Worksheets("MRE").Range("E13").Copy (Worksheets("sauvegarde").Cells(ligVide, 8))
    Worksheets("MRE").Range("F13").Copy (Worksheets("sauvegarde").Cells(ligVide, 9))
    Worksheets("MRE").Range("G13").Copy (Worksheets("sauvegarde").Cells(ligVide, 10))
            ligVide = Sheets("sauvegarde").Range("D" & Rows.Count).End(xlUp).Row + 1
    Worksheets("MRE").Range("G3").Copy (Worksheets("sauvegarde").Cells(ligVide, 1))
    Worksheets("MRE").Range("B8").Copy (Worksheets("sauvegarde").Cells(ligVide, 2))
    Worksheets("MRE").Range("F8").Copy (Worksheets("sauvegarde").Cells(ligVide, 3))
    Worksheets("MRE").Range("A14").Copy (Worksheets("sauvegarde").Cells(ligVide, 4))
    Worksheets("MRE").Range("B14").Copy (Worksheets("sauvegarde").Cells(ligVide, 5))
    Worksheets("MRE").Range("F7").Copy (Worksheets("sauvegarde").Cells(ligVide, 6))
    Worksheets("MRE").Range("D14").Copy (Worksheets("sauvegarde").Cells(ligVide, 7))
    Worksheets("MRE").Range("E14").Copy (Worksheets("sauvegarde").Cells(ligVide, 8))
    Worksheets("MRE").Range("F14").Copy (Worksheets("sauvegarde").Cells(ligVide, 9))
    Worksheets("MRE").Range("G14").Copy (Worksheets("sauvegarde").Cells(ligVide, 10))
        ligVide = Sheets("sauvegarde").Range("D" & Rows.Count).End(xlUp).Row + 1
    Worksheets("MRE").Range("G3").Copy (Worksheets("sauvegarde").Cells(ligVide, 1))
    Worksheets("MRE").Range("B8").Copy (Worksheets("sauvegarde").Cells(ligVide, 2))
    Worksheets("MRE").Range("F8").Copy (Worksheets("sauvegarde").Cells(ligVide, 3))
    Worksheets("MRE").Range("A15").Copy (Worksheets("sauvegarde").Cells(ligVide, 4))
    Worksheets("MRE").Range("B15").Copy (Worksheets("sauvegarde").Cells(ligVide, 5))
    Worksheets("MRE").Range("F7").Copy (Worksheets("sauvegarde").Cells(ligVide, 6))
    Worksheets("MRE").Range("D15").Copy (Worksheets("sauvegarde").Cells(ligVide, 7))
    Worksheets("MRE").Range("E15").Copy (Worksheets("sauvegarde").Cells(ligVide, 8))
    Worksheets("MRE").Range("F15").Copy (Worksheets("sauvegarde").Cells(ligVide, 9))
    Worksheets("MRE").Range("G15").Copy (Worksheets("sauvegarde").Cells(ligVide, 10))
        ligVide = Sheets("sauvegarde").Range("D" & Rows.Count).End(xlUp).Row + 1
    Worksheets("MRE").Range("G3").Copy (Worksheets("sauvegarde").Cells(ligVide, 1))
    Worksheets("MRE").Range("B8").Copy (Worksheets("sauvegarde").Cells(ligVide, 2))
    Worksheets("MRE").Range("F8").Copy (Worksheets("sauvegarde").Cells(ligVide, 3))
    Worksheets("MRE").Range("A16").Copy (Worksheets("sauvegarde").Cells(ligVide, 4))
    Worksheets("MRE").Range("B16").Copy (Worksheets("sauvegarde").Cells(ligVide, 5))
    Worksheets("MRE").Range("F7").Copy (Worksheets("sauvegarde").Cells(ligVide, 6))
    Worksheets("MRE").Range("D16").Copy (Worksheets("sauvegarde").Cells(ligVide, 7))
    Worksheets("MRE").Range("E16").Copy (Worksheets("sauvegarde").Cells(ligVide, 8))
    Worksheets("MRE").Range("F16").Copy (Worksheets("sauvegarde").Cells(ligVide, 9))
    Worksheets("MRE").Range("G16").Copy (Worksheets("sauvegarde").Cells(ligVide, 10))
            ligVide = Sheets("sauvegarde").Range("D" & Rows.Count).End(xlUp).Row + 1
    Worksheets("MRE").Range("G3").Copy (Worksheets("sauvegarde").Cells(ligVide, 1))
    Worksheets("MRE").Range("B8").Copy (Worksheets("sauvegarde").Cells(ligVide, 2))
    Worksheets("MRE").Range("F8").Copy (Worksheets("sauvegarde").Cells(ligVide, 3))
    Worksheets("MRE").Range("A17").Copy (Worksheets("sauvegarde").Cells(ligVide, 4))
    Worksheets("MRE").Range("B17").Copy (Worksheets("sauvegarde").Cells(ligVide, 5))
    Worksheets("MRE").Range("F7").Copy (Worksheets("sauvegarde").Cells(ligVide, 6))
    Worksheets("MRE").Range("D17").Copy (Worksheets("sauvegarde").Cells(ligVide, 7))
    Worksheets("MRE").Range("E17").Copy (Worksheets("sauvegarde").Cells(ligVide, 8))
    Worksheets("MRE").Range("F17").Copy (Worksheets("sauvegarde").Cells(ligVide, 9))
    Worksheets("MRE").Range("G17").Copy (Worksheets("sauvegarde").Cells(ligVide, 10))
        ligVide = Sheets("sauvegarde").Range("D" & Rows.Count).End(xlUp).Row + 1
    Worksheets("MRE").Range("G3").Copy (Worksheets("sauvegarde").Cells(ligVide, 1))
    Worksheets("MRE").Range("B8").Copy (Worksheets("sauvegarde").Cells(ligVide, 2))
    Worksheets("MRE").Range("F8").Copy (Worksheets("sauvegarde").Cells(ligVide, 3))
    Worksheets("MRE").Range("A18").Copy (Worksheets("sauvegarde").Cells(ligVide, 4))
    Worksheets("MRE").Range("B18").Copy (Worksheets("sauvegarde").Cells(ligVide, 5))
    Worksheets("MRE").Range("F7").Copy (Worksheets("sauvegarde").Cells(ligVide, 6))
    Worksheets("MRE").Range("D18").Copy (Worksheets("sauvegarde").Cells(ligVide, 7))
    Worksheets("MRE").Range("E18").Copy (Worksheets("sauvegarde").Cells(ligVide, 8))
    Worksheets("MRE").Range("F18").Copy (Worksheets("sauvegarde").Cells(ligVide, 9))
    Worksheets("MRE").Range("G18").Copy (Worksheets("sauvegarde").Cells(ligVide, 10))
        ligVide = Sheets("sauvegarde").Range("D" & Rows.Count).End(xlUp).Row + 1
    Worksheets("MRE").Range("G3").Copy (Worksheets("sauvegarde").Cells(ligVide, 1))
    Worksheets("MRE").Range("B8").Copy (Worksheets("sauvegarde").Cells(ligVide, 2))
    Worksheets("MRE").Range("F8").Copy (Worksheets("sauvegarde").Cells(ligVide, 3))
    Worksheets("MRE").Range("A19").Copy (Worksheets("sauvegarde").Cells(ligVide, 4))
    Worksheets("MRE").Range("B19").Copy (Worksheets("sauvegarde").Cells(ligVide, 5))
    Worksheets("MRE").Range("F7").Copy (Worksheets("sauvegarde").Cells(ligVide, 6))
    Worksheets("MRE").Range("D19").Copy (Worksheets("sauvegarde").Cells(ligVide, 7))
    Worksheets("MRE").Range("E19").Copy (Worksheets("sauvegarde").Cells(ligVide, 8))
    Worksheets("MRE").Range("F19").Copy (Worksheets("sauvegarde").Cells(ligVide, 9))
    Worksheets("MRE").Range("G19").Copy (Worksheets("sauvegarde").Cells(ligVide, 10))
            ligVide = Sheets("sauvegarde").Range("D" & Rows.Count).End(xlUp).Row + 1
    Worksheets("MRE").Range("G3").Copy (Worksheets("sauvegarde").Cells(ligVide, 1))
    Worksheets("MRE").Range("B8").Copy (Worksheets("sauvegarde").Cells(ligVide, 2))
    Worksheets("MRE").Range("F8").Copy (Worksheets("sauvegarde").Cells(ligVide, 3))
    Worksheets("MRE").Range("A20").Copy (Worksheets("sauvegarde").Cells(ligVide, 4))
    Worksheets("MRE").Range("B20").Copy (Worksheets("sauvegarde").Cells(ligVide, 5))
    Worksheets("MRE").Range("F7").Copy (Worksheets("sauvegarde").Cells(ligVide, 6))
    Worksheets("MRE").Range("D20").Copy (Worksheets("sauvegarde").Cells(ligVide, 7))
    Worksheets("MRE").Range("E20").Copy (Worksheets("sauvegarde").Cells(ligVide, 8))
    Worksheets("MRE").Range("F20").Copy (Worksheets("sauvegarde").Cells(ligVide, 9))
    Worksheets("MRE").Range("G20").Copy (Worksheets("sauvegarde").Cells(ligVide, 10))
        ligVide = Sheets("sauvegarde").Range("D" & Rows.Count).End(xlUp).Row + 1
    Worksheets("MRE").Range("G3").Copy (Worksheets("sauvegarde").Cells(ligVide, 1))
    Worksheets("MRE").Range("B8").Copy (Worksheets("sauvegarde").Cells(ligVide, 2))
    Worksheets("MRE").Range("F8").Copy (Worksheets("sauvegarde").Cells(ligVide, 3))
    Worksheets("MRE").Range("A21").Copy (Worksheets("sauvegarde").Cells(ligVide, 4))
    Worksheets("MRE").Range("B21").Copy (Worksheets("sauvegarde").Cells(ligVide, 5))
    Worksheets("MRE").Range("F7").Copy (Worksheets("sauvegarde").Cells(ligVide, 6))
    Worksheets("MRE").Range("D21").Copy (Worksheets("sauvegarde").Cells(ligVide, 7))
    Worksheets("MRE").Range("E21").Copy (Worksheets("sauvegarde").Cells(ligVide, 8))
    Worksheets("MRE").Range("F21").Copy (Worksheets("sauvegarde").Cells(ligVide, 9))
    Worksheets("MRE").Range("G21").Copy (Worksheets("sauvegarde").Cells(ligVide, 10))
            ligVide = Sheets("sauvegarde").Range("D" & Rows.Count).End(xlUp).Row + 1
    Worksheets("MRE").Range("G3").Copy (Worksheets("sauvegarde").Cells(ligVide, 1))
    Worksheets("MRE").Range("B8").Copy (Worksheets("sauvegarde").Cells(ligVide, 2))
    Worksheets("MRE").Range("F8").Copy (Worksheets("sauvegarde").Cells(ligVide, 3))
    Worksheets("MRE").Range("A22").Copy (Worksheets("sauvegarde").Cells(ligVide, 4))
    Worksheets("MRE").Range("B22").Copy (Worksheets("sauvegarde").Cells(ligVide, 5))
    Worksheets("MRE").Range("F7").Copy (Worksheets("sauvegarde").Cells(ligVide, 6))
    Worksheets("MRE").Range("D22").Copy (Worksheets("sauvegarde").Cells(ligVide, 7))
    Worksheets("MRE").Range("E22").Copy (Worksheets("sauvegarde").Cells(ligVide, 8))
    Worksheets("MRE").Range("F22").Copy (Worksheets("sauvegarde").Cells(ligVide, 9))
    Worksheets("MRE").Range("G22").Copy (Worksheets("sauvegarde").Cells(ligVide, 10))
        ligVide = Sheets("sauvegarde").Range("D" & Rows.Count).End(xlUp).Row + 1
    Worksheets("MRE").Range("G3").Copy (Worksheets("sauvegarde").Cells(ligVide, 1))
    Worksheets("MRE").Range("B8").Copy (Worksheets("sauvegarde").Cells(ligVide, 2))
    Worksheets("MRE").Range("F8").Copy (Worksheets("sauvegarde").Cells(ligVide, 3))
    Worksheets("MRE").Range("A23").Copy (Worksheets("sauvegarde").Cells(ligVide, 4))
    Worksheets("MRE").Range("B23").Copy (Worksheets("sauvegarde").Cells(ligVide, 5))
    Worksheets("MRE").Range("F7").Copy (Worksheets("sauvegarde").Cells(ligVide, 6))
    Worksheets("MRE").Range("D23").Copy (Worksheets("sauvegarde").Cells(ligVide, 7))
    Worksheets("MRE").Range("E23").Copy (Worksheets("sauvegarde").Cells(ligVide, 8))
    Worksheets("MRE").Range("F23").Copy (Worksheets("sauvegarde").Cells(ligVide, 9))
    Worksheets("MRE").Range("G23").Copy (Worksheets("sauvegarde").Cells(ligVide, 10))
            ligVide = Sheets("sauvegarde").Range("D" & Rows.Count).End(xlUp).Row + 1
    Worksheets("MRE").Range("G3").Copy (Worksheets("sauvegarde").Cells(ligVide, 1))
    Worksheets("MRE").Range("B8").Copy (Worksheets("sauvegarde").Cells(ligVide, 2))
    Worksheets("MRE").Range("F8").Copy (Worksheets("sauvegarde").Cells(ligVide, 3))
    Worksheets("MRE").Range("A24").Copy (Worksheets("sauvegarde").Cells(ligVide, 4))
    Worksheets("MRE").Range("B24").Copy (Worksheets("sauvegarde").Cells(ligVide, 5))
    Worksheets("MRE").Range("F7").Copy (Worksheets("sauvegarde").Cells(ligVide, 6))
    Worksheets("MRE").Range("D24").Copy (Worksheets("sauvegarde").Cells(ligVide, 7))
    Worksheets("MRE").Range("E24").Copy (Worksheets("sauvegarde").Cells(ligVide, 8))
    Worksheets("MRE").Range("F24").Copy (Worksheets("sauvegarde").Cells(ligVide, 9))
    Worksheets("MRE").Range("G24").Copy (Worksheets("sauvegarde").Cells(ligVide, 10))
     ligVide = Sheets("sauvegarde").Range("D" & Rows.Count).End(xlUp).Row + 1
    Worksheets("MRE").Range("G3").Copy (Worksheets("sauvegarde").Cells(ligVide, 1))
    Worksheets("MRE").Range("B8").Copy (Worksheets("sauvegarde").Cells(ligVide, 2))
    Worksheets("MRE").Range("F8").Copy (Worksheets("sauvegarde").Cells(ligVide, 3))
    Worksheets("MRE").Range("A25").Copy (Worksheets("sauvegarde").Cells(ligVide, 4))
    Worksheets("MRE").Range("B25").Copy (Worksheets("sauvegarde").Cells(ligVide, 5))
    Worksheets("MRE").Range("F7").Copy (Worksheets("sauvegarde").Cells(ligVide, 6))
    Worksheets("MRE").Range("D25").Copy (Worksheets("sauvegarde").Cells(ligVide, 7))
    Worksheets("MRE").Range("E25").Copy (Worksheets("sauvegarde").Cells(ligVide, 8))
    Worksheets("MRE").Range("F25").Copy (Worksheets("sauvegarde").Cells(ligVide, 9))
    Worksheets("MRE").Range("G25").Copy (Worksheets("sauvegarde").Cells(ligVide, 10))
        ligVide = Sheets("sauvegarde").Range("D" & Rows.Count).End(xlUp).Row + 1
    Worksheets("MRE").Range("G3").Copy (Worksheets("sauvegarde").Cells(ligVide, 1))
    Worksheets("MRE").Range("B8").Copy (Worksheets("sauvegarde").Cells(ligVide, 2))
    Worksheets("MRE").Range("F8").Copy (Worksheets("sauvegarde").Cells(ligVide, 3))
    Worksheets("MRE").Range("A26").Copy (Worksheets("sauvegarde").Cells(ligVide, 4))
    Worksheets("MRE").Range("B26").Copy (Worksheets("sauvegarde").Cells(ligVide, 5))
    Worksheets("MRE").Range("F7").Copy (Worksheets("sauvegarde").Cells(ligVide, 6))
    Worksheets("MRE").Range("D26").Copy (Worksheets("sauvegarde").Cells(ligVide, 7))
    Worksheets("MRE").Range("E26").Copy (Worksheets("sauvegarde").Cells(ligVide, 8))
    Worksheets("MRE").Range("F26").Copy (Worksheets("sauvegarde").Cells(ligVide, 9))
    Worksheets("MRE").Range("G26").Copy (Worksheets("sauvegarde").Cells(ligVide, 10))
            ligVide = Sheets("sauvegarde").Range("D" & Rows.Count).End(xlUp).Row + 1
    Worksheets("MRE").Range("G3").Copy (Worksheets("sauvegarde").Cells(ligVide, 1))
    Worksheets("MRE").Range("B8").Copy (Worksheets("sauvegarde").Cells(ligVide, 2))
    Worksheets("MRE").Range("F8").Copy (Worksheets("sauvegarde").Cells(ligVide, 3))
    Worksheets("MRE").Range("A27").Copy (Worksheets("sauvegarde").Cells(ligVide, 4))
    Worksheets("MRE").Range("B27").Copy (Worksheets("sauvegarde").Cells(ligVide, 5))
    Worksheets("MRE").Range("F7").Copy (Worksheets("sauvegarde").Cells(ligVide, 6))
    Worksheets("MRE").Range("D27").Copy (Worksheets("sauvegarde").Cells(ligVide, 7))
    Worksheets("MRE").Range("E27").Copy (Worksheets("sauvegarde").Cells(ligVide, 8))
    Worksheets("MRE").Range("F27").Copy (Worksheets("sauvegarde").Cells(ligVide, 9))
    Worksheets("MRE").Range("G27").Copy (Worksheets("sauvegarde").Cells(ligVide, 10))
        ligVide = Sheets("sauvegarde").Range("D" & Rows.Count).End(xlUp).Row + 1
    Worksheets("MRE").Range("G3").Copy (Worksheets("sauvegarde").Cells(ligVide, 1))
    Worksheets("MRE").Range("B8").Copy (Worksheets("sauvegarde").Cells(ligVide, 2))
    Worksheets("MRE").Range("F8").Copy (Worksheets("sauvegarde").Cells(ligVide, 3))
    Worksheets("MRE").Range("A28").Copy (Worksheets("sauvegarde").Cells(ligVide, 4))
    Worksheets("MRE").Range("B28").Copy (Worksheets("sauvegarde").Cells(ligVide, 5))
    Worksheets("MRE").Range("F7").Copy (Worksheets("sauvegarde").Cells(ligVide, 6))
    Worksheets("MRE").Range("D28").Copy (Worksheets("sauvegarde").Cells(ligVide, 7))
    Worksheets("MRE").Range("E28").Copy (Worksheets("sauvegarde").Cells(ligVide, 8))
    Worksheets("MRE").Range("F28").Copy (Worksheets("sauvegarde").Cells(ligVide, 9))
    Worksheets("MRE").Range("G28").Copy (Worksheets("sauvegarde").Cells(ligVide, 10))
        ligVide = Sheets("sauvegarde").Range("D" & Rows.Count).End(xlUp).Row + 1
    Worksheets("MRE").Range("G3").Copy (Worksheets("sauvegarde").Cells(ligVide, 1))
    Worksheets("MRE").Range("B8").Copy (Worksheets("sauvegarde").Cells(ligVide, 2))
    Worksheets("MRE").Range("F8").Copy (Worksheets("sauvegarde").Cells(ligVide, 3))
    Worksheets("MRE").Range("A29").Copy (Worksheets("sauvegarde").Cells(ligVide, 4))
    Worksheets("MRE").Range("B29").Copy (Worksheets("sauvegarde").Cells(ligVide, 5))
    Worksheets("MRE").Range("F7").Copy (Worksheets("sauvegarde").Cells(ligVide, 6))
    Worksheets("MRE").Range("D29").Copy (Worksheets("sauvegarde").Cells(ligVide, 7))
    Worksheets("MRE").Range("E29").Copy (Worksheets("sauvegarde").Cells(ligVide, 8))
    Worksheets("MRE").Range("F29").Copy (Worksheets("sauvegarde").Cells(ligVide, 9))
    Worksheets("MRE").Range("G29").Copy (Worksheets("sauvegarde").Cells(ligVide, 10))
            ligVide = Sheets("sauvegarde").Range("D" & Rows.Count).End(xlUp).Row + 1
    Worksheets("MRE").Range("G3").Copy (Worksheets("sauvegarde").Cells(ligVide, 1))
    Worksheets("MRE").Range("B8").Copy (Worksheets("sauvegarde").Cells(ligVide, 2))
    Worksheets("MRE").Range("F8").Copy (Worksheets("sauvegarde").Cells(ligVide, 3))
    Worksheets("MRE").Range("A30").Copy (Worksheets("sauvegarde").Cells(ligVide, 4))
    Worksheets("MRE").Range("B30").Copy (Worksheets("sauvegarde").Cells(ligVide, 5))
    Worksheets("MRE").Range("F7").Copy (Worksheets("sauvegarde").Cells(ligVide, 6))
    Worksheets("MRE").Range("D30").Copy (Worksheets("sauvegarde").Cells(ligVide, 7))
    Worksheets("MRE").Range("E30").Copy (Worksheets("sauvegarde").Cells(ligVide, 8))
    Worksheets("MRE").Range("F30").Copy (Worksheets("sauvegarde").Cells(ligVide, 9))
    Worksheets("MRE").Range("G30").Copy (Worksheets("sauvegarde").Cells(ligVide, 10))
        ligVide = Sheets("sauvegarde").Range("D" & Rows.Count).End(xlUp).Row + 1
    Worksheets("MRE").Range("G3").Copy (Worksheets("sauvegarde").Cells(ligVide, 1))
    Worksheets("MRE").Range("B8").Copy (Worksheets("sauvegarde").Cells(ligVide, 2))
    Worksheets("MRE").Range("F8").Copy (Worksheets("sauvegarde").Cells(ligVide, 3))
    Worksheets("MRE").Range("A31").Copy (Worksheets("sauvegarde").Cells(ligVide, 4))
    Worksheets("MRE").Range("B31").Copy (Worksheets("sauvegarde").Cells(ligVide, 5))
    Worksheets("MRE").Range("F7").Copy (Worksheets("sauvegarde").Cells(ligVide, 6))
    Worksheets("MRE").Range("D31").Copy (Worksheets("sauvegarde").Cells(ligVide, 7))
    Worksheets("MRE").Range("E31").Copy (Worksheets("sauvegarde").Cells(ligVide, 8))
    Worksheets("MRE").Range("F31").Copy (Worksheets("sauvegarde").Cells(ligVide, 9))
    Worksheets("MRE").Range("G31").Copy (Worksheets("sauvegarde").Cells(ligVide, 10))


 
End Sub

sylvanu · 18 Juin 2020

Bonjour Jay,
Un essai en PJ,
Je me suis permis d'un peu simplifié votre macro.
J'ai supposé que l'alerte était quand un ou plusieurs ** étaient présent.

jay72000 · 18 Juin 2020

Bonjour Sylvanu, un grand merci !! C'est parfait

_Thierry · 18 Juin 2020

Bonjour @jay72000 , @sylvanu , le Forum

Je pense qu'il vaut mieux spécifier comme ceci sur la Macro de Sylvanu :

VB:

'''''For i = 12 To 31
For i = 12 To Worksheets("MRE").Range("A1000").End(xlUp).Row

Car déjà le tableau va jusqu'à 41, et pas 31, et puis comme ca on y pense plus...

Bonne journée
@+Thierry

sylvanu · 18 Juin 2020

Bonjour Thierry,
Exact.
On peut aussi simplifier tous ces Worksheets("sauvegarde") à répétition.
Mais ça suppose qu'il n'y ait rien d'autre après la ligne 41.

_Thierry · 18 Juin 2020

Re Sylvanu, Jay

Et en fait on voit bien ici l'importance d'avoir des tables les plus structurées possibles pour s'éviter des gymnastiques infernales en cas d'Algos qui doivent travailler par dessus...

Toujours en continuant la tentative d'élimination de toute cette série de Worksheets("sauvegarde") à répétition. On finit par se compliquer la vie un max...

Pour simplifier ceci :

VB:

    With Worksheets("MRE")
        f.Cells(ligVide, 1) = .Range("G3")
        f.Cells(ligVide, 2) = .Range("B8")
        f.Cells(ligVide, 3) = .Range("F8")
        f.Cells(ligVide, 4) = .Range("A" & i)
        f.Cells(ligVide, 5) = .Range("B" & i)
        f.Cells(ligVide, 6) = .Range("F7")
        f.Cells(ligVide, 7) = .Range("D" & i)
        f.Cells(ligVide, 8) = .Range("E" & i)
        f.Cells(ligVide, 9) = .Range("F" & i)
        f.Cells(ligVide, 10) = .Range("G" & i)
    End With

Ca finit par donner cet algo à la limite du rocambolesque LoL !!!

Code:

    With Worksheets("MRE")
        For c = 1 To 10
        Select Case c
        Case 1, 2, 3, 6
            f.Cells(ligVide, 1) = .Range("G3")
            f.Cells(ligVide, 2) = .Range("B8")
            f.Cells(ligVide, 3) = .Range("F8")
            f.Cells(ligVide, 6) = .Range("F7")
        Case Else
            For x = 1 To 7
                If x <> 3 Then
                    f.Cells(ligVide, x + 3) = .Cells(i, x)
                End If
            Next x
        End Select
        Next c
    End With

Mais bon , on est mieux que dans le Post #1 quand même !!!

Just for the fn !

Bon aprèm
@+Thierry

XL 2013 Macro de recopie sans copier les formules juste les valeurs et ajouter conditions

jay72000

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

Pièces jointes

jay72000

XLDnaute Nouveau

_Thierry

XLDnaute Barbatruc

sylvanu

XLDnaute Barbatruc

Pièces jointes

_Thierry

XLDnaute Barbatruc

Discussions similaires