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
| Sub concatenationTimeSheets()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim i As Integer, j As Integer, nbTasks As Integer, debutTasks As Integer, indiceTaskACopier As Integer, indiceTasksDejaCopiees As Integer
Dim continue As Boolean
Dim TimeSheet(11) As Workbook, wbKPI As Workbook
Dim Trigrammes As Variant
Dim wdEnCours As String
Set wbKPI = ThisWorkbook
Trigrammes = Array("TDC", "PMY", "BBI", "SSI", "LPR", "FCN", "ABN", "PDZ", "SSY", "CJY", "ALT")
debutTasks = 3 'indique la ligne de début des tasks à copier
indiceTasksDejaCopiees = 0
For i = LBound(Trigrammes) To UBound(Trigrammes)
'A ajuster avec le nom complet exact de tes fichiers
wbEnCours = ".\Time_sheet_2014_" & Trigrammes(i) & ".xlsx"
'Tu ouvres la timesheet que tu vas copier
Set TimeSheet(i) = Application.Workbooks.Open(wbEnCours, False)
'Tu copies toutes les tasks de la sheet TOT
indiceTaskACopier = debutTasks
continue = True
With TimeSheet(i).Sheets("TOT")
While (continue = True)
If Left(.Cells(indiceTaskACopier, 2).Value, 4) = "Task" Then
indiceTaskACopier = indiceTaskACopier + 1
Else
continue = False
End If
Wend
.Rows(debutTasks & ":" & indiceTaskACopier - 1).Copy
End With
wbKPI.Sheets("Data").Rows(5 + indiceTasksDejaCopiees).PasteSpecial xlValues
indiceTasksDejaCopiees = indiceTasksDejaCopiees + indiceTaskACopier - debutTasks
Next
Application.CutCopyMode = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False
End Sub |
Partager