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
| Sub Enregistre()
Dim Chemin As String, Fichier As String, Fact As String
Dim Wbk As Workbook
Dim Sh As Worksheet
Application.ScreenUpdating = False
Chemin = ThisWorkbook.Path & "\" 'Dossier de sauvegarde = celui du fichier FACTURE
Fichier = Format(Date, "dd-mm-yyyy") & ".xls" 'nom du fichier archive
Fact = Worksheets("FACTURE").Range("B9").Value 'N° Facture
If Dir(Chemin & Fichier) = "" Then 'Si le classeur n'existe pas, on le crée et on nomme la première feuille avec le N° de facture
Set Wbk = Workbooks.Add(1)
Set Sh = Wbk.Worksheets(1)
Sh.Name = Fact
Wbk.SaveAs Chemin & Fichier
Else 'Si le classeur existe, on l'ouvre
Set Wbk = Workbooks.Open(Chemin & Fichier)
If Not Existe(Wbk, Fact) Then 'Si la feuille N° Facture n'existe pas, on l'ajoute dans le classeur qu'on vient d'ouvrir
Set Sh = Wbk.Worksheets.Add(after:=Wbk.Sheets(Wbk.Sheets.Count))
Sh.Name = Fact
Else
Set Sh = Wbk.Worksheets(Fact)
End If
End If
ThisWorkbook.Worksheets("FACTURE").Range("A8:F37").Copy Sh.Range("A1")
Sh.UsedRange.Value = Sh.UsedRange.Value
Set Sh = Nothing
Wbk.Close True
Set Wbk = Nothing
Efface
End Sub
Private Function Existe(ByVal Wbk As Workbook, ByVal Str As String) As Boolean
Dim Sh As Worksheet
For Each Sh In Wbk.Sheets
If UCase(Sh.Name) = UCase(Str) Then
Existe = True
Exit For
End If
Next Sh
End Function
Sub Efface()
Dim NewLig As Long
Application.ScreenUpdating = False
With Worksheets("Z JOURNALIER")
NewLig = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Range("A:B").UnMerge
With .Range("A" & NewLig)
.Value = Worksheets("FACTURE").Range("B9").Value
.HorizontalAlignment = xlCenter
.Borders.Weight = xlThin
End With
.Range("B" & NewLig).Value = Worksheets("FACTURE").Range("F36").Value
.Range("C" & NewLig).Value = Worksheets("FACTURE").Range("F37").Value
With .Range("B" & NewLig & ":C" & NewLig)
.NumberFormat = "#,##0.00 $"
.Borders.Weight = xlThin
End With
End With
With Worksheets("FACTURE")
With .Range("A16:E35")
.UnMerge
.ClearContents
.Merge True
End With
.Range("F16:F35") = ""
Union(.Range("A10:A12"), .Range("A14:A15"), .Range("B13"), .Range("D10"), .Range("D13"), .Range("F13")).Value = "/"
With .Range("A16:F36").Font
.Name = "Calibri"
.Size = "11"
.Bold = False
End With
.Range("B9").Value = Val(.Range("B9")) + 1
End With
End Sub |
Partager