Sub test()
Dim cell As Range
Dim rng As Range
Dim nom As String
Dim derligne
With Application
.ScreenUpdating = False
.Calculation = xlManual
End With
derligne = Sheets("REF").Cells(Rows.Count, 10).End(xlUp).Row
For Each cell In Range("A1:AK1")
On Error Resume Next
If Left(cell.Value, 5) = "taskw" And cell.Offset(0, 1).Value = "TOP RO" And cell.Value <> Sheets(Sheets.Count).Name Then
Debug.Print cell.Value
Columns(cell.Offset(0, 1).Column).EntireColumn.Insert
Sheets("REF").Range(cell.Offset(0, 1).Address).Value = Sheets(Sheets.Count).Name
Range(cell.Offset(1, 1).Address).Formula = "=IFERROR(VLOOKUP(RC1," & Sheets(Sheets.Count).Name & "!R2C1:R51C8,8,FALSE),""Non existant"")"
Range(cell.Offset(1, 1).Address).AutoFill Destination:=Range(Cells(2, cell.Offset(0, 1).Column), Cells(derligne, cell.Offset(0, 1).Column))
End If
Next cell
With Application
.ScreenUpdating = True
.Calculation = xlAutomatic
End With
End Sub