Option Explicit
Sub Auto_Data()
Dim chemin, nomfichier, fichierlu, fenêtrelue As String
Dim xvi3k1, xvi3k2, xvi3k3, xvi3k4, xvi3k5 As Variant
Dim xvi3k6, xvi3k7, xvi3k8, xvi3k9, xvi3k10 As Variant
Dim xvi3k11, xvi3k12, xvi3k13, xvi3k14, xvi3k15, xvi3k16 As Variant
Dim sn_VI3, Date_VI3, Operator_VI3 As Variant
Dim i As Long
Application.ScreenUpdating = True
Sheets("Data").Select
Range("A2").Select
chemin = ThisWorkbook.Path
nomfichier = ActiveWorkbook.Name
Range("A1").Select
With Application.FileSearch
.LookIn = chemin
.SearchSubFolders = True
.Filename = "ATR_Test*.xls"
If .Execute > 0 Then
Range("A2:AH1000").Select
Selection.ClearContents
Range("A2").Select
For i = 1 To .FoundFiles.Count
fichierlu = .FoundFiles(i)
If Right(fichierlu, 3) = "xls" Then
Workbooks.Open Filename:=fichierlu, ReadOnly:=True
fenêtrelue = ActiveWorkbook.Name
Worksheets("Rev1").Activate
If ActiveSheet.Name <> "Rev1" Then
ActiveWindow.Close SaveChanges:=False
Exit Sub
End If
' sélection à copier
sn_VI3 = Range("G2").Value 'Serial Number
Date_VI3 = Range("G3").Value 'Date
Operator_VI3 = Range("B3").Value 'Operator
xvi3k1 = Range("D5").Value
xvi3k2 = Range("D6").Value
xvi3k3 = Range("D7").Value
xvi3k4 = Range("D8").Value
xvi3k5 = Range("D10").Value
xvi3k6 = Range("D11").Value
xvi3k7 = Range("D12").Value
xvi3k8 = Range("D13").Value
xvi3k9 = Range("D14").Value
xvi3k10 = Range("D16").Value
xvi3k11 = Range("D17").Value
xvi3k12 = Range("D18").Value
xvi3k13 = Range("D19").Value
xvi3k14 = Range("D20").Value
xvi3k15 = Range("D21").Value
xvi3k16 = Range("D22").Value
ActiveWindow.Close SaveChanges:=False
'mettre à la suite
ActiveCell.Value = sn_VI3
ActiveCell.Offset(0, 1).Value = Date_VI3
ActiveCell.Offset(0, 2).Value = Operator_VI3
ActiveCell.Offset(0, 3).Value = xvi3k1
ActiveCell.Offset(0, 4).Value = xvi3k2
ActiveCell.Offset(0, 5).Value = xvi3k3
ActiveCell.Offset(0, 6).Value = xvi3k4
ActiveCell.Offset(0, 7).Value = xvi3k5
ActiveCell.Offset(0, 8).Value = xvi3k6
ActiveCell.Offset(0, 9).Value = xvi3k7
ActiveCell.Offset(0, 10).Value = xvi3k8
ActiveCell.Offset(0, 11).Value = xvi3k9
ActiveCell.Offset(0, 12).Value = xvi3k10
ActiveCell.Offset(0, 13).Value = xvi3k11
ActiveCell.Offset(0, 14).Value = xvi3k12
ActiveCell.Offset(0, 15).Value = xvi3k13
ActiveCell.Offset(0, 16).Value = xvi3k14
ActiveCell.Offset(1, 0).Select
End If
Next i
End If
End With
End Sub