XL 2016 Autofill Destination sur colonne adjacente

Sub LastOnglet()

Dim NomOnglet4 As String
Dim NomOnglet5 As String
Dim NomOnglet6 As String
Dim NomOnglet7 As String
Dim NomOnglet8 As String
Dim Lr As Long
Dim Cel As Range

NomOnglet4 = Sheets(4).Name
NomOnglet5 = Sheets(5).Name
NomOnglet6 = Sheets(6).Name
NomOnglet7 = Sheets(7).Name
NomOnglet8 = Sheets(8).Name


Sheets("Recap Détection").Activate
Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Value = "Collaborateur"
With Range("A1:A2")
.Interior.ColorIndex = 35
.Merge
End With

' Onglet Valérie
Sheets("Valérie").Activate
Lr = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Valérie").Range("A2:C" & Lr).Copy
Sheets("Recap détection").Activate
Range("B2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A2").Value = NomOnglet6
Range("A2").AutoFill Destination:=Range("A2:A" & Lr), Type:=xlFillDefault

' Onglet Nathalie
Sheets("Nathalie").Activate
Lr = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Nathalie").Range("A2:C" & Lr).Copy
Sheets("Recap détection").Activate

Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveCell.Value = NomOnglet5
Lr = Range("B" & Rows.Count).End(xlUp).Row
Set Cel = ActiveCell
ActiveCell.Copy
ActiveCell.AutoFill Destination:=Range("a" & Lr)

End Sub

Cliquez pour agrandir...