Sub FRNS_Brut_To_Balance()
Application.ScreenUpdating = False
With Sheets("FRNS BALANCE")
.UsedRange.Offset(1, 0).Delete
End With
With Sheets("FRNS BRUT")
.Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1)), TrailingMinusNumbers:=True
.Columns("B:F").Copy Destination:=Sheets("FRNS BALANCE").Range("N:N")
.Columns("M:N").Copy Destination:=Sheets("FRNS BALANCE").Range("S:S")
.Cells.Clear
End With
With Sheets("FRNS BALANCE")
formuleY = "=Concatener(P2;Q2;R2)"
formuleZ = "=N2"
formuleAF = "=RECHERCHEV(Y2;'SHARE pour coms'!$W$2:$AD$532;8;FAUX)"
fin = .Range("N" & .Rows.Count).End(xlUp).Row
.Range("Y2").FormulaLocal = formuleY
.Range("Z2").FormulaLocal = formuleZ
.Range("Z2:AE2").FillRight
.Range("AF2").FormulaLocal = formuleAF
.Range("Y2:AF" & fin).FillDown
.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub