Sub consolider2()
Dim i As Long
Dim fichie As Variant
Dim wkb1 As Worksheet
Dim lr As Long
Dim shF As Worksheet
Dim classeur
Dim Région As String
Dim District As String
Dim Etab As String
Dim TabSource0(), TabSource1(), TabSource2(), TabSource3() As Variant
Dim deb As Long
Application.ScreenUpdating = False
Set shF = ThisWorkbook.Worksheets("BASE")
ChDir ActiveWorkbook.Path
fichie = Application.GetOpenFilename(Title:="Selectionnez le Fichier de données à importer", filefilter:="Fichier Excel (*.xls*),*xlsm*", buttontext:="Cliquez")
If fichie <> False Then
Set classeur = Application.Workbooks.Open(fichie)
deb = Timer
With classeur
For i = 7 To (6 + 13)
With .Worksheets(i)
TabSource0 = .Range("C12:C27").Value
TabSource1 = .Range("D12:AL27").Value
TabSource2 = .Range("D33:AL48").Value
TabSource3 = .Range("D54:AL69").Value
Région = .Range("J5")
District = .Range("O5")
Etab = .Range("V5")
End With
lr = shF.Range("A" & Rows.Count).End(xlUp).Row + 1
shF.Range("A" & lr).Resize(16) = Région
shF.Range("B" & lr).Resize(16) = District
shF.Range("C" & lr).Resize(16) = Etab
shF.Range("D" & lr).Resize(UBound(TabSource0, 1)) = TabSource0
shF.Range("E" & lr).Resize(16, 35) = TabSource1
shF.Range("AN" & lr).Resize(16, 35) = TabSource2
shF.Range("BW" & lr).Resize(16, 35) = TabSource3
Set wkb1 = Nothing
Next i
End With
classeur.Close SaveChanges:=False
Else
MsgBox "Pas d'ONC selectionné !!!"
Exit Sub
End If
Application.ScreenUpdating = True
MsgBox Timer - deb
End Sub