1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62
|
Sub subMAJSuivi()
ActiveSheet.Unprotect "VCGP"
Dim SousRep As String
SousRep = ThisWorkbook.Path & "\"
ChDir SousRep
Classeur_com = Dir(SousRep & "*.xlsx*")
While Len(Classeur_com) > 0
Workbooks.Open Classeur_com
Dim shSuivi As Excel.Worksheet, shSource As Excel.Worksheet
Dim L1Suivi As Long, L1Source As Long, LDSuivi As Long, LDSource As Long
Dim iBT As Integer, sNumBT As String
Dim x As Long, y As Long, z As Long, w As Long
Set shSuivi = Application.Workbooks(Classeur_com).Worksheets("Business projects list")
Set shSource = Application.ThisWorkbook.Worksheets("Business projects list")
L1Suivi = 1
L1Source = 6
iBT = 1 'numéro colonne #BT
LDSuivi = shSuivi.Cells(Application.Rows.Count, iBT).End(xlUp).Row
LDSource = shSource.Cells(Application.Rows.Count, iBT).End(xlUp).Row
For x = L1Suivi To LDSuivi
sNumBT = shSuivi.Cells(x, iBT).Value
For y = L1Source To LDSource
z = shSource.Cells(y, iBT).Row
If shSource.Cells(y, iBT).Value = sNumBT Then shSuivi.Rows(x).Copy shSource.Rows(z)
Next y
For w = L1Source To LDSource
If shSource.Cells(w, iBT).Value = sNumBT Then Exit For
Next w
If w > LDSource Then
LDSource = LDSource + 1
shSuivi.Rows(x).Copy shSource.Rows(LDSource)
End If
Next x
Set shSource = Nothing
Set shSuivi = Nothing
Workbooks(Classeur_com).Close
Classeur_com = Dir
Wend
ActiveSheet.Protect "VCGP"
End Sub |
Partager