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 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137
| Sub recupdonnées()
Dim fdest As Worksheet
Dim fsource As Excel.Workbook
Dim Wksheet As Worksheet
Dim derligne As Integer, nbligne As Integer, ldest As Integer, i As Integer, j As Integer, dcelpleine As Integer
Dim objectif As Variant
Dim nom As String
Set fdest = ThisWorkbook.Worksheets(1)
Set fsource = xls
ThisWorkbook.Sheets(1).Select
Application.ScreenUpdating = False
Call clear
For Each Wksheet In fsource.Worksheets
Select Case Wksheet.Name
Case Is = "Feuille de Saisie"
Case Is = "Feuil1"
Case Else
test = Wksheet.checkbox1.Value
If test = False Then
derligne = Wksheet.Range("A69").End(xlUp).Row
nbligne = ((derligne - 15) / 2) + 1
If nbligne = 0 Then GoTo fsuivante
objectif = Wksheet.Cells(11, 9).Value
nom = Wksheet.Range("A3").Value
dcelpleine = fdest.Range("B65536").End(xlUp).Row
If dcelpleine = 1 Then
ldest = 1
Else
ldest = dcelpleine + 2
End If
Cells(ldest + 1, 1).Value = "Semaine"
Cells(ldest + 2, 1).Value = "Réalisé"
Cells(ldest + 3, 1).Value = "Cumul Réalisé"
Cells(ldest + 4, 1).Value = "RAF"
Cells(ldest + 5, 1).Value = "% Réalisé"
Cells(ldest + 6, 1).Value = "% Chiffrage"
Cells(ldest + 1, 2).Value = ""
Cells(ldest + 2, 2).Value = 0
Cells(ldest + 3, 2).Value = 0
Cells(ldest + 4, 2).Value = objectif
Cells(ldest + 5, 2).Value = 0
Cells(ldest + 6, 2).Value = 0
realise = 0
j = 3
For i = 15 To derligne Step 2
fdest.Cells(ldest + 1, j).Value = Wksheet.Cells(i, 1).Value
fdest.Cells(ldest + 2, j).Value = Wksheet.Cells(i, 29).Value
realise = realise + Wksheet.Cells(i, 29).Value
fdest.Cells(ldest + 3, j).Value = realise
fdest.Cells(ldest + 4, j).Value = Wksheet.Cells(i, 33).Value
fdest.Cells(ldest + 5, j).Value = Wksheet.Cells(i, 37).Value * 100
fdest.Cells(ldest + 6, j).Value = (realise / objectif) * 100
j = j + 1
Next i
If Cells(ldest + 5, j - 1).Value = 100 Then
fdest.Range(Cells(ldest, 2), Cells(ldest, nbligne + 2)).Merge
With Cells(ldest, 2)
.Value = nom
.Interior.ColorIndex = 36
End With
With Cells(ldest, 1)
.Value = objectif
.Interior.ColorIndex = 37
End With
With Range(Cells(ldest, 1), Cells(ldest + 6, nbligne + 2))
.Borders.LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlInsideHorizontal).Weight = xlThin
.Borders(xlInsideVertical).Weight = xlThin
End With
Else
Cells(ldest + 3, j).Value = realise + Cells(ldest + 4, derligne).Value
Cells(ldest + 4, j).Value = 0
Cells(ldest + 5, j).Value = 100
Cells(ldest + 6, j).Value = (Cells(ldest + 3, derligne + 1).Value * 100) / objectif
fdest.Range(Cells(ldest, 2), Cells(ldest, nbligne + 3)).Merge
With Cells(ldest, 2)
.Value = nom
.Interior.ColorIndex = 36
End With
With Cells(ldest, 1)
.Value = objectif
.Interior.ColorIndex = 37
End With
With Range(Cells(ldest, 1), Cells(ldest + 6, nbligne + 3))
.Borders.LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlInsideHorizontal).Weight = xlThin
.Borders(xlInsideVertical).Weight = xlThin
End With
End If
End If
End Select
fsuivante:
Next Wksheet
Application.DisplayAlerts = False
fsource.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub |
Partager