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
| ActiveWorkbook.SaveAs Filename:=nomdechemin _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="scale", _
ReadOnlyRecommended:=True, CreateBackup:=False
'copie valeur & format
Windows("GESTION 07 BETA1.xls").Activate '#ARRET
fbl.Activate
Range("A1:M59").Select
Range("M1").Activate
Selection.Copy
Windows(nomdefichier & ".xls").Activate
Range("A1:M59").Select
ActiveSheet.Paste
Selection.PasteSpecial xlPasteFormats, xlPasteSpecialOperationNone, False, False
'definition des paramètres d'impression
ActiveSheet.PageSetup.PrintArea = "$A$1:$M$59"
With ActiveSheet.PageSetup '#ARRET
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0) '#ARRET
.TopMargin = Application.InchesToPoints(0.393700787401575)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0.984251968503937)
.FooterMargin = Application.InchesToPoints(0.393700787401575)
.CenterHorizontally = True
.CenterVertically = True
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Range("A1:M59").Select
Range("M1").Activate
ActiveSheet.PageSetup.PrintArea = "$A$1:$M$59"
Range("K1").Select '#ARRET
bl.ProgressBarsave.Value = 40
' remplissage de la feuille bl
With ActiveWorkbook.ActiveSheet
.Cells(3, 10) = bl.datedecommande.Value
.Cells(8, 7) = bl.nomclient.Value & Chr(10) & bl.adr1.Value & Chr(10) & bl.adr2.Value & Chr(10) & bl.cp.Value & " " & bl.ville.Value
' .Cells(11, 4) = Mid(CStr(Now), 4, 2)
' .Cells(11, 6) = Mid(CStr(Now), 1, 2)
' .Cells(11, 7) = Mid(CStr(Now), 9, 2)
.Cells(11, 4) = bl.datedecommande.Month
.Cells(11, 6) = bl.datedecommande.Day
.Cells(11, 7) = Right(CStr(bl.datedecommande.Year), 2)
.Cells(11, 9) = numerosaff.Cells(ligne, 4)
.Cells(11, 10) = numerosaff.Cells(ligne, 5)
.Cells(11, 12) = nbbl + 1
.Cells(15, 6) = bl.codefournisseur.Value
.Cells(16, 7) = bl.tva_intercom.Value
bl.ProgressBarsave.Value = 50
'gestion des détail du BL
'gestion du multi-numaff
.Cells(17, 3) = numerosaff.Cells(ligne, 1)
.Cells(17, 4) = numerosaff.Cells(ligne, 2)
.Cells(17, 5) = numerosaff.Cells(ligne, 3)
.Cells(17, 6) = numerosaff.Cells(ligne, 4)
.Cells(17, 7) = numerosaff.Cells(ligne, 5)
.Cells(17, 8) = "av " & numerosaff.Cells(ligne, 6) '#ARRET
.Cells(17, 9) = "ind" & Right(detailcommande.Cells(ligne - 6, 2), 1)
bl.ProgressBarsave.Value = 60
i = 0
For listecom = 0 To bl.commande.ListCount - 1
If bl.commande.Selected(listecom) = True Then
If i >= 1 Then
.Rows(i + 13 + 1 & ":" & i + 13 + 1).Insert Shift:=xlUp
.Range(Cells(i + 13, 5), Cells(i + 13, 9)).Merge
.Range(Cells(i + 13, 10), Cells(i + 13, 12)).Merge
.Rows("42:42").Delete Shift:=xlDown
End If
lignecom2 = detailcommande.Range("C:C").Find(bl.commande.List(listecom), detailcommande.Cells(1, 3), xlValues, xlWhole, xlByRows, xlNext).Row
detailcommande.Cells(lignecom2, detailcommande.Cells(lignecom2, Columns.Count).End(xlToLeft).Column) = nomdechemin & ".xls"
.Cells(i + 13, 5) = detailcommande.Cells(lignecom2, 3)
.Cells(i + 13, 10) = detailcommande.Cells(lignecom2, 4)
i = i + 1
End If
Next
bl.ProgressBarsave.Value = 80
listecom = i
.Range(.Cells(22 + listecom, 1), .Cells(42, 13)).ClearContents
For i = 21 + listecom To 42
.Cells(i, 1) = variable.Cells(i - (17 + listecom), 1)
.Cells(i, 8) = variable.Cells(i - (17 + listecom), 2)
.Cells(i, 10) = variable.Cells(i - (17 + listecom), 3)
If .Cells(i, 8) <> "" Then .Cells(i, 13) = .Cells(i, 8) * .Cells(i, 10)
Next
.Cells(49, 2).FormulaLocal = "=SOMME(L(-" & 27 - listecom & ")C(11):L(-7)C(11))"
bl.ProgressBarsave.Value = 90
' fbl.Range("C25:M42").ClearContents
' For temp = 0 To bl.designation.ListCount - 1
' fbl.Cells(25 + temp, 3) = bl.designation.List(bl.designation.ListCount - temp - 1)
' fbl.Cells(25 + temp, 13) = bl.cout.List(bl.designation.ListCount - temp - 1)
' Next
End With
'impression, sauvegarde & fermeture
'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWorkbook.Save
'Application.DisplayAlerts = False
ActiveWorkbook.Close '#ARRET
Workbooks.Open Filename:=nomdechemin, UpdateLinks:=0, ReadOnly:=True, IgnoreReadOnlyRecommended:=False, Notify:=False
bl.ProgressBarsave.Value = 100
bl.choixsite.Clear
bl.Hide
' defdetbl.Show
menu.Show |
