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
| Sub Macro2()
'
'Ouvrire boite de dialogue
Application.Dialogs(xlDialogOpen).Show
'copie du fichier
Range("A1:E57618").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets.Add before:=Sheets(Sheets.Count)
ActiveSheet.Paste
Sheets("Feuil1").Select
Sheets("Feuil1").Name = "donner traiter"
'suppresion d'une ligne sur deux
Dim i As Long
For i = Range("A" & Rows.Count).End(xlUp).Row To 20 Step -2
Rows(i).Delete
Next i
'copie des cycles
Set Plage = Sheets("donner traiter").Range("A18")
Set Plage = Range(Plage, Plage.End(xlDown))
Plage.Copy
'Création des feuilles et collage
nbfeuilles = Sheets("donner traiter").Range("C11")
For i = 1 To nbfeuilles
Sheets.Add after:=Sheets(Worksheets.Count)
Sheets(Worksheets.Count).Name = "Cylindre" & i
Range("A2").PasteSpecial xlPasteAll
Next i
Application.CutCopyMode = False
'copie des cycles et copie sur chaque feuilles
nbcylindre = Sheets("donner traiter").Range("C11")
For i = 1 To nbcylindre
Set Plage = Sheets("donner traiter").Cells(17, 1 + i)
Set Plage = Range(Plage, Plage.End(xlDown))
Plage.Copy
Sheets("cylindre" & (i)).Cells(1, 2).PasteSpecial
Next i
Application.CutCopyMode = False
Dim nbcycle As Integer
nbcycle = Sheets("donner traiter").Range("C12")
Dim cyi As Integer
Dim bloci As Integer
'Boucle sur les cylindres
For cyi = 1 To nbcylindre
Worksheets("cylindre" & (cyi)).Cells(1, 3 + bloci).Value = "cycle" & bloci & "/" & nbcycle
'Boucle sur les blocs de 720 pts
For bloci = 0 To nbcycle - 1
With Worksheets("donner traiter")
.Range(.Cells(19 + bloci * 720, cyi + 1), .Cells(16 + (bloci + 1) * 720, cyi + 1)).Copy
End With
'créations des titres
With Worksheets("cylindre" & (cyi))
.Cells(1, 3 + bloci).Value = "Cycle" & (bloci + 1)
.Cells(2, 3 + bloci).PasteSpecial (xlPasteValues)
End With
Next bloci
Next cyi
'création titre Moyenne
For cyi = 1 To nbcylindre
With Worksheets("cylindre" & (cyi))
.Cells(1, nbcycle + 3).Value = "Moyenne"
End With
Next cyi
'création des moyennes
For cyi = 1 To nbcylindre
With Worksheets("cylindre" & (cyi))
For i = 1 To 720
If Application.WorksheetFunction.Sum(.Range(.Cells(i, 3), .Cells(i, nbcycle + 3))) = 0 Then
.Cells(i + 1, nbcycle + 3).Value = "VIDE"
Else
.Cells(i + 1, nbcycle + 3).Value = Application.Average(.Range(.Cells(i + 1, 3), .Cells(i + 1, nbcycle + 3)))
End If
Next
End With
Next |
Partager