Option Explicit
Option Base 1
Public ListCol() As Variant
Public DerLgn As Long
Public i As Byte
Public Lgn As Byte
Public c As Range
Public ColCible As Byte
Public ColSource As Byte
Sub extract()
'*****************
Recup_Tab 'On va récupérer le tableau des Entêtes et Lignes prédéfinis
'*****************
Application.ScreenUpdating = False
Sheets("BaseT").Range("A9").CurrentRegion.ClearContents 'On efface les données présentent
With Sheets("BaseO") 'Avec la feuille
DerLgn = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 1 To UBound(ListCol, 1) 'on récupère les numéros de colonnes à extraire de BaseO
Set c = .Rows("6:6").Find(ListCol(i, 1)) 'On recherhe l'entete en ligne 6
If Not c Is Nothing Then 'Si trouvé
ColCible = CByte(ListCol(i, 3)) 'On récupére le Numero de la colonne Cible "Feuille BaseT"
ColSource = c.Column 'On récupére le Numero de la colonne Source "Feuille BaseO"
Sheets("BaseT").Cells(9, ColCible) = ListCol(i, 2) 'On colle l'entête abrege
.Cells(7, ColSource).Resize(DerLgn).Copy Destination:=Sheets("BaseT").Cells(10, ColCible) 'On colle les donnees de la colonne ainsi définie
Else
CodeAbsent = ListCol(i, 1) & ";" & CodeAbsent
End If
Next i
End With
Application.ScreenUpdating = True
If CodeAbsent <> "" Then MsgBox ("les intitulés de colonnes suivants n'ont pas été trouvés:") & Chr(10) & CodeAbsent
End Sub
Public Function Recup_Tab()
ReDim Preserve ListCol(11, 4)
ListCol(1, 1) = "Test": ListCol(1, 2) = "DPT": ListCol(1, 3) = 1: ListCol(1, 4) = ""
'ListCol(1, 1) = "DEL.Code Département": ListCol(1, 2) = "DPT": ListCol(1, 3) = 1: ListCol(1, 4) = ""
ListCol(2, 1) = "SIT.Code Site Dpt": ListCol(2, 2) = "SIT": ListCol(2, 3) = 2: ListCol(2, 4) = ""
ListCol(3, 1) = "SIT.Code compte": ListCol(3, 2) = "CPT": ListCol(3, 3) = 3: ListCol(3, 4) = ""
ListCol(4, 1) = "SIT.Code compte SRC": ListCol(4, 2) = "SRC": ListCol(4, 3) = 4: ListCol(4, 4) = ""
ListCol(5, 1) = "BAT.Régime type Statut": ListCol(5, 2) = "STAT": ListCol(5, 3) = 5: ListCol(5, 4) = ""
ListCol(6, 1) = "BAT.Code Bâtiment Dpt": ListCol(6, 2) = "BAT": ListCol(6, 3) = 6: ListCol(6, 4) = ""
ListCol(7, 1) = "BAT.SHON": ListCol(7, 2) = "SHON": ListCol(7, 3) = 7: ListCol(7, 4) = ""
ListCol(8, 1) = "BAT.SUB": ListCol(8, 2) = "SUB": ListCol(8, 3) = 8: ListCol(8, 4) = ""
ListCol(9, 1) = "BAT.SUN": ListCol(9, 2) = "SUN": ListCol(9, 3) = 9: ListCol(9, 4) = ""
ListCol(10, 1) = "Année construction": ListCol(10, 2) = "An.construction": ListCol(10, 3) = 10: ListCol(10, 4) = ""
ListCol(11, 1) = "Date de réhabilitation": ListCol(11, 2) = "Date Réha.": ListCol(11, 3) = 11: ListCol(11, 4) = ""
End Function