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
| Private Sub ComboBox1_Change()
NumeroEnr1.Value = Range("'BON'!T4") + 1
Dim Cell As Range
End Sub
Sub Bouton1_QuandClic()
ActiveWorkbook.Unprotect Password:=""
Sheets("BON").Visible = True
Sheets("BON").Select
Application.WindowState = xlMaximized
' Impression du fichier
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
' Transfert des données dans le tableau de BPC
Sheets("BPC").Activate
Sheets("BON").Select
Range("O3").Copy Worksheets("BPC").Range("A2")
Range("S3").Copy Worksheets("BPC").Range("B2")
Range("O7").Copy Worksheets("BPC").Range("C2")
Range("O9").Copy Worksheets("BPC").Range("D2")
Range("O11").Copy Worksheets("BPC").Range("E2")
Range("O13").Copy Worksheets("BPC").Range("F2")
Range("R13").Copy Worksheets("BPC").Range("G2")
Range("O15").Copy Worksheets("BPC").Range("H2")
Range("R15").Copy Worksheets("BPC").Range("I2")
Range("H24").Copy Worksheets("BPC").Range("J2")
Range("H26").Copy Worksheets("BPC").Range("K2")
Range("Q26").Copy Worksheets("BPC").Range("L2")
Range("I30").Copy Worksheets("BPC").Range("M2")
Range("K32").Copy Worksheets("BPC").Range("N2")
Range("K34").Copy Worksheets("BPC").Range("N2")
Range("O34").Copy Worksheets("BPC").Range("O2")
Range("E38").Copy Worksheets("BPC").Range("P2")
Range("E40").Copy Worksheets("BPC").Range("Q2")
Range("K42").Copy Worksheets("BPC").Range("R2")
Range("E50").Copy Worksheets("BPC").Range("S2")
Range("E52").Copy Worksheets("BPC").Range("S2")
Range("E54").Copy Worksheets("BPC").Range("S2")
Range("N60").Copy Worksheets("BPC").Range("T2")
Range("O63").Copy Worksheets("BPC").Range("U2")
'macro d'impression du BON
Sheets("BON").Select
'Sheets("BON").Copy
'Application.DisplayAlerts = False
'Dim a As Integer
'Dim b As Integer
'a = Range("'BON'!T5")
'b = Range(" 'BON'!T6")
'ActiveWorkbook.SaveAs ("C:\Users\beaurepaire\Desktop\Bon de Prise en Charge\TEST\BPC NOUMEA\BPC)
' Sauvegarge du fichier Excel et quitte l'application
Sheets("BON").Protect Password:=""
ActiveWorkbook.Save
Application.Quit
End Sub
2/
La réinitialisation c’est bon.
Dés que je réunis les deux il ne se passe que la 1ere étape sans agrémentation.
Sub Bouton1_QuandClic()
Sheets("BON").Unprotect Password:=""
' Impression du fichier
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
' Transfert des données dans le tableau de bon de prise en charge
Range("A5000:U5000").Copy
Sheets("BPC").Activate
Range("'BPC'!A65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("BON").Select
Call Clear1
Sheets("BON").Protect Password:=""
End Sub
Sub Clear1()
'macro d'impression du BPC
Sheets("BON").Select
'Sheets("BON").Copy
'Application.DisplayAlerts = False
'Dim a As Integer
'Dim b As Integer
'a = Range("'BON'!T5")
'b = Range(" 'BON'!T6")
'ActiveWorkbook.SaveAs ()
' Nettoyage du tableau
Range("O7,O9,O11,O13,R13,O15,R15,H24,H26,Q26,I30,K32,K34,O34,E38,E40,K42,E50,I54,N60,O63,A147").Select
Selection.ClearContents
' Sauvegarge du fichier Excel et quitte l'application
Sheets("BON").Protect Password:=""
ActiveWorkbook.Save
Application.Quit
End Sub |
