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
|
'*********************************************************************************************
Public Sub Generer_Debitage() 'BOUTON GÉNÉRER, CRÉER ET REMPLIR LES AUTRE PAGES SELON LES OPTION COCHÉS
'*********************************************************************************************
Dim i As Integer, j As Integer, k As Integer, Page As Integer, Bkp(14) As Boolean, PageBkp As Integer
Dim NonFonction As String, NomParametre As String
Dim N_PAGE(14) As String 'TABLEAU DES NOM DE PAGE
Dim Ligne_page(14, 1) As Integer 'TABLEAU DES LIGNE / PAGE
Page = Worksheets("LISTE").Cells(1, 26).Value
N_PAGE(9) = "DEBITAGE"
N_PAGE(10) = "PLASMA"
N_PAGE(11) = "FACONNAGE"
N_PAGE(12) = "USINAGE"
N_PAGE(13) = "ASSEMBLAGE"
For i = 9 To 13 ' REMPLIR TABLEAU LIGNE / PAGE
For j = 0 To 1
Ligne_page(i, j) = 1
Next
Next
Application.ScreenUpdating = False
Bkp(0) = False
'BACKUP DES PAGES ET INFORMATIONS EXISTANTES
If Page_Exist("DEBITAGE") Or Page_Exist("PLASMA") Or Page_Exist("FACONNAGE") Or Page_Exist("USINAGE") Or Page_Exist("ASSEMBLAGE") Then
If MsgBox("voulez vous conserver les données saisis?", vbQuestion + vbYesNo + vbDefaultButton1, "Attention") = vbYes Then
Bkp(0) = True
If Page_Exist("DEBITAGE") Then ' SI LA PAGE DEBITAGE EXISTE. BACKUP
Bkp(9) = True
Dim Backup_Data_Debitage() As String 'DÉCLARATION DE VARIABLE
PageBkp = Worksheets("DEBITAGE").Cells(1, 30).Value ' VA LIRE LE NOMBRE DE PAGE
ReDim Backup_Data_Debitage(PageBkp * 15, 10) ' DÉFINI LA VARIABLE AU NOMBRE DE PAGE
Backup_Data_Debitage = Backup_Debitage(PageBkp) 'BACKUP DES DONNÉES
Application.DisplayAlerts = False
Worksheets("DEBITAGE").Delete ' EFFACE LA PAGE
Application.DisplayAlerts = True
End If
If Page_Exist("PLASMA") Then
Bkp(10) = True
Dim Backup_Data_PLASMA() As String
PageBkp = Worksheets("PLASMA").Cells(1, 30).Value
ReDim Backup_Data_PLASMA(PageBkp * 15, 7)
Backup_Data_PLASMA = Backup_Plasma(PageBkp)
Application.DisplayAlerts = False
Worksheets("PLASMA").Delete
Application.DisplayAlerts = True
End If
If Page_Exist("FACONNAGE") Then
Bkp(11) = True
Dim Backup_Data_Faconnage() As String
PageBkp = Worksheets("FACONNAGE").Cells(1, 30).Value
ReDim Backup_Data_Faconnage(PageBkp * 15, 12)
Backup_Data_Faconnage = Backup_Faconnage(PageBkp)
Application.DisplayAlerts = False
Worksheets("FACONNAGE").Delete
Application.DisplayAlerts = True
End If
If Page_Exist("USINAGE") Then
Bkp(12) = True
Dim Backup_Data_Usinage() As String
PageBkp = Worksheets("USINAGE").Cells(1, 30).Value
ReDim Backup_Data_Usinage(PageBkp * 15, 9)
Backup_Data_Usinage = Backup_Usinage(PageBkp)
Application.DisplayAlerts = False
Worksheets("USINAGE").Delete
Application.DisplayAlerts = True
End If
If Page_Exist("ASSEMBLAGE") Then
Bkp(13) = True
Dim Backup_Data_Assemblage() As String
PageBkp = Worksheets("ASSEMBLAGE").Cells(1, 30).Value
ReDim Backup_Data_Assemblage(PageBkp * 15, 8)
Backup_Data_Assemblage = Backup_Assemblage(PageBkp)
Application.DisplayAlerts = False
Worksheets("ASSEMBLAGE").Delete
Application.DisplayAlerts = True
End If
End If
End If
'DEBUT DE REMPLISSAGE
For i = 1 To Page 'pour chaque page
For j = 1 To 30 Step 2 ' pour chaqe ligne
For k = 9 To 13 ' pour chaque colone
If Worksheets("liste").Cells((i - 1) * 31 + 7 + j, k).Value <> "" Then ' SI LA CELLUNE N'EST PAS VIDE
If Not Page_Exist(N_PAGE(k)) Then 'SI LA PAGE CORREPONDANTE N'EXISTE PAS, LA CRÉER
Creer_Page (N_PAGE(k)) 'CRÉER LA PAGE
Worksheets(N_PAGE(k)).Cells(1, 30) = 1 ' NOMBRE DE PAGE
Remplir_Entete (N_PAGE(k)) 'REMPLIR L'ENTETE
Remplir_Controle (N_PAGE(k)) 'REMPLIR LES CONTROLE DE QUALITÉ
End If
If Ligne_page(k, 0) >= 30 Then ' SI PAGE PLEINE
Worksheets(N_PAGE(k)).Unprotect 'ENLEVE LA PROTECTION
Ajouter_Page N_PAGE(k), Ligne_page(k, 1)
Ligne_page(k, 1) = Ligne_page(k, 1) + 1
Worksheets(N_PAGE(k)).Cells(1, 30) = Ligne_page(k, 1) 'INSCRIT LE NOMBRE DE PAGE
Ligne_page(k, 0) = 1
Worksheets(N_PAGE(k)).Protect AllowFormattingCells:=True ' ACTIVE LA PROTECTION
Worksheets(N_PAGE(k)).Range("I8").Activate
End If
Worksheets(N_PAGE(k)).Cells((Ligne_page(k, 1) - 1) * 31 + 7 + Ligne_page(k, 0), 1).Value _
= Worksheets("liste").Cells((i - 1) * 31 + 7 + j, 1).Value 'ITEM
Worksheets(N_PAGE(k)).Cells((Ligne_page(k, 1) - 1) * 31 + 7 + Ligne_page(k, 0), 2).Value _
= Worksheets("liste").Cells((i - 1) * 31 + 7 + j, 2).Value 'QTE
Worksheets(N_PAGE(k)).Cells((Ligne_page(k, 1) - 1) * 31 + 7 + Ligne_page(k, 0), 3).Value _
= Worksheets("liste").Cells((i - 1) * 31 + 7 + j, 3).Value 'DESCRIPTION
Worksheets(N_PAGE(k)).Cells((Ligne_page(k, 1) - 1) * 31 + 7 + Ligne_page(k, 0), 7).Value _
= Worksheets("liste").Cells((i - 1) * 31 + 7 + j, 7).Value 'GRADE
If Bkp(0) And Bkp(k) Then 'si un backup a été pris
NonFonction = "Restaure_" & N_PAGE(k)
NomParametre = "Backup_Data_" & N_PAGE(k)
Application.Run NonFonction, (Ligne_page(k, 1) - 1) * 31 + 7 + Ligne_page(k, 0), NomParametre
End If
Ligne_page(k, 0) = Ligne_page(k, 0) + 2
End If
Next
Next
Next
Application.ScreenUpdating = True
End Sub |
