Private Sub CommandButton2_Click()
Worksheets("BL_EPURE").Range("A:S").Clear
Sheets("BL").Columns("A:S").Copy Sheets("BL_EPURE").Columns(1)
'Efface la seconde ligne à la 1000 ème
With Worksheets("IMPORT_TMP")
.Rows("2:1000").EntireRow.Delete
End With
'Efface la seconde ligne à la 1000 ème
With Worksheets("IMPORT_LIGNE")
.Rows("2:1000").EntireRow.Delete
End With
Application.ScreenUpdating = False
Dim Lg As Integer
For Lg = Sheets("BL_EPURE").UsedRange.Rows.Count To 2 Step -1
If Sheets("BL_EPURE").Range("L" & Lg) <> Sheets("Variables").Range("B4") _
Then Sheets("BL_EPURE").Rows(Lg).Delete
If Sheets("IMPORT_TMP").Range("B" & Lg) = "*" _
Then Sheets("IMPORT_TMP").Rows(Lg).Delete
If Sheets("IMPORT_TMP").Range("B" & Lg) = "" _
Then Sheets("IMPORT_TMP").Rows(Lg).Delete
Next Lg
For Lg = Sheets("BL_EPURE").UsedRange.Rows.Count To 2 Step -1
Worksheets("BL_EPURE").Range("T" & Lg).Value = "=IF(ISNA(VLOOKUP(RC[-17],SERIEM!C[-14]:C[-2],2,FALSE)),""*"",CONCAT(VLOOKUP(RC[-17],SERIEM!C[-14]:C[-2],2,FALSE),REPT("" "",20-LEN(VLOOKUP(RC[-17],SERIEM!C[-14]:C[-2],2,FALSE)))))"
Worksheets("BL_EPURE").Range("U" & Lg).Value = "=IF(ISNA(VLOOKUP(RC[-18],SERIEM!C[-15]:C[-3],2,FALSE)),""*"",VLOOKUP(RC[-18],SERIEM!C[-15]:C[-3],7,FALSE)/VLOOKUP(RC[-18],SERIEM!C[-15]:C[-3],6,FALSE))"
Worksheets("BL_EPURE").Range("V" & Lg).Value = "=IF(ISNA(VLOOKUP(RC[-19],SERIEM!C[-16]:C[-4],2,FALSE)),""*"",RC[-14])"
Worksheets("BL_EPURE").Range("W" & Lg).Value = "=IF(ISNA(VLOOKUP(RC[-20],SERIEM!C[-17]:C[-5],2,FALSE)),""*"",RC[-2]-RC[-1])"
Worksheets("IMPORT_LIGNE").Range("A" & Lg).Value = "01 "
Worksheets("IMPORT_LIGNE").Range("B" & Lg).Value = Worksheets("BL_EPURE").Range("T" & Lg).Value
Worksheets("IMPORT_LIGNE").Range("C" & Lg).Value = Worksheets("BL_EPURE").Range("U" & Lg).Value
Worksheets("IMPORT_LIGNE").Range("D" & Lg).Value = Worksheets("BL_EPURE").Range("V" & Lg).Value
Worksheets("IMPORT_LIGNE").Range("E" & Lg).Value = "P"
Worksheets("IMPORT_TMP").Range("A" & Lg).Value = "01 "
Worksheets("IMPORT_TMP").Range("B" & Lg).Value = Worksheets("BL_EPURE").Range("T" & Lg).Value
Worksheets("IMPORT_TMP").Range("C" & Lg).Value = Worksheets("BL_EPURE").Range("U" & Lg).Value
Worksheets("IMPORT_TMP").Range("D" & Lg).Value = Worksheets("BL_EPURE").Range("V" & Lg).Value
Worksheets("IMPORT_TMP").Range("E" & Lg).Value = "P"
If Sheets("IMPORT_TMP").Range("B" & Lg) = "*" _
Then Sheets("IMPORT_TMP").Rows(Lg).Delete
If Sheets("IMPORT_TMP").Range("B" & Lg) = "" _
Then Sheets("IMPORT_TMP").Rows(Lg).Delete
Next Lg
Sheets("IMPORT_ENTETE").Activate
Sheets("IMPORT_ENTETE").Rows("1:1").Select
Selection.Copy
Sheets("IMPORT_TMP").Activate
Sheets("IMPORT_TMP").Rows("1:1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Variables").Select
Worksheets("Variables").Range("F8").Interior.Color = RGB(255, 235, 156)
Worksheets("Variables").Range("F8").Value = "Fait"
End Sub