Option Explicit
Public c As Range, lepath As String, lenom As String, fichier As String, dossier As String, chemin As String, nouvonglet As String, DLePath As String, n, choix
Private Sub commandbutton1_click()
With Application: .ScreenUpdating = False: .Calculation = xlManual: .EnableEvents = False: End With
If Range("e2") = "Facture" Then
[g5].FormulaR1C1 = "=INDEX(Clients!C[-6],MATCH('Facture Pro Forma'!R[7]C[-5],Clients!C[-5],0))"
[e4] = "Facture n°"
[h67].Name = "TTC"
With Range("az1"): .Value = Range("f5").Value: .NumberFormat = "ddmmyyyy": End With
Range("l1") = Range("l1") + 1
Range("e5") = Range("g5") & " " & Range("f5") & Range("l1")
If Range("f5") <> Date Then choix = MsgBox("La date en f5 est erronée. Souhaites-tu la conserver ?", vbYesNo, "Attention, petit poisson...")
If choix = vbNo Then Exit Sub
For Each c In Range("aa1:aa50").SpecialCells(xlCellTypeConstants)
If c.Value = Range("b12").Value And c.Offset(, 1) = Range("f5").Value Then
MsgBox "Attention, M'sieur : facture en doublon !"
Range("l1") = Range("l1") - 1
Exit Sub
End If
Next
Range("aa" & Rows.Count).End(xlUp)(2) = [b12]
Range("ab" & Rows.Count).End(xlUp)(2) = [f5]
lepath = "C:\Users\00\Downloads\" 'emplacement adapter
ActiveSheet.Copy
ActiveSheet.Name = Left([b12], 19) & " du " & [az1].Text
'ActiveSheet.Name = [b12].Value & " du " & [a1].Text 'pas ok si nom onglet > 31 caractères
' ActiveSheet.Name = [B12].Value & " du " & Format([f5], "ddmmyyyy") ' pas ok chez Calvus
ActiveSheet.Shapes.Range(Array("CommandButton1", "CommandButton2", _
"ToggleButton1", "ToggleButton2", "CommandButton3", "CommandButton4")).Delete
For Each c In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 23)
c.Value = c.Value
Next
For Each n In ActiveWorkbook.Names
n.Delete
Next
Application.DisplayAlerts = False
lenom = [b12] & " F du " & Format([f5], "ddmmyyyy") & ".xlsx"
With ActiveWorkbook: .SaveAs lepath & lenom: .Close: End With
Application.DisplayAlerts = True
Sheets("Facture Pro Forma").Copy Before:=Sheets(4)
Workbooks.Open ("C:\Users\00\Downloads\00 - Calvus - Historique F.xlsm") 'emplacement adapter
Windows("00 - Calvus - F et FP.xlsm").Activate
Sheets("Facture Pro Forma (2)").Select
Application.DisplayAlerts = False
Sheets("Facture Pro Forma (2)").Move after:=Workbooks("00 - Calvus - Historique F.xlsm").Sheets(1) 'emplacement adapter
For Each n In ActiveWorkbook.Names
n.Delete
Next
Application.DisplayAlerts = True
ActiveSheet.Name = Left([b12], 19) & " du " & [az1].Text
ActiveWindow.Zoom = 80
ActiveWorkbook.Close True
Sheets("Facture Pro Forma").Select
Range("a1:h67").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\00\Downloads\" & Range("b12") & " Facture du " & Format([f5], "ddmmyyyy") & ".pdf", quality:=xlQualityStandard, _
includedocproperties:=True, ignoreprintareas:=False, openafterpublish:=False ' emplacement adapter
With Feuil15.Range("b" & Rows.Count).End(xlUp)(2)
.Offset(0, 0) = Feuil20.Range("g5")
.Offset(0, 1) = Feuil20.Range("b12")
.Offset(0, 2) = Feuil20.Range("c15")
.Offset(0, 3).FormulaR1C1 = "=VLOOKUP(RC[-3],Clients!C[-4]:C[9],13,0)"
.Offset(0, 4).FormulaR1C1 = "=VLOOKUP(RC[-4],Clients!C[-5]:C[8],14,0)"
.Offset(0, 5) = Range("TTC")
.Offset(0, 6) = Feuil20.Range("f5")
.Columns("b:h").EntireColumn.AutoFit
End With
With Sheets("Ventes").Range("b2").CurrentRegion
.Borders.Value = 1
.Interior.Color = 15853019
.BorderAround xlContinuous, xlMedium, xlColorIndexAutomatic
End With
With Sheets("Ventes").Range("b2:h2"): .Interior.Color = 12419407: .Font.Bold = True: .Font.ColorIndex = 2: _
.BorderAround xlContinuous, xlMedium, xlColorIndexAutomatic: End With
End If
With Application: .EnableEvents = True: .Calculation = xlAutomatic: .ScreenUpdating = True: End With
End Sub