Sub AjoutDevis()
Dim i, Ind As Integer, LigSel As Long, NomSht As String
Dim ShtTdB As Worksheet, fich As String
Set ShtTdB = Sheets("TableauDeBord")
For i = 1 To 1000
[A2].Offset(i).Select
LigSel = Selection.Row
For Ind = 0 To 4
If ShtTdB.Cells(LigSel, 1 + Ind).Value = "" Then
MsgBox "Le devis ne peut pas être créé, il manque une information" _
, vbExclamation, "Attention..."
ShtTdB.Cells(LigSel, 1 + Ind).Select
Exit Sub
End If
Next Ind
NomSht = Range("A" & LigSel)
fich = ThisWorkbook.Path & "\" & NomSht 'chemin à adapter
If Dir(fich & ".xls*") <> "" Then _
MsgBox "Le devis a déjà été créé...": Exit Sub
For Ind = 1 To Len(NomSht)
If InStr("][\/:*?""<>|", Mid(NomSht, Ind, 1)) Then _
MsgBox "Les caractères ] [ \ / : * ? "" <> | sont interdits...": Exit Sub
Next
Application.ScreenUpdating = False 'c'est mieux
ThisWorkbook.Unprotect "Rachid" 'mot de passe
With Sheets("DEVIS_V")
.Visible = True
.Copy 'nouveau document
.Visible = False
End With
ThisWorkbook.Protect "Rachid"
With ActiveWorkbook.Sheets(1)
.Name = Left(NomSht, 31)
.Range("J1") = NomSht
.Range("J2") = ShtTdB.Range("B" & LigSel)
.Range("B1") = ShtTdB.Range("C" & LigSel)
.Range("B2") = ShtTdB.Range("D" & LigSel)
.Range("H44") = ShtTdB.Range("E" & LigSel)
.Parent.SaveAs fich
fich = Dir(fich & ".xls*")
ShtTdB.Cells(LigSel, 9) = "=VLOOKUP(""*TTC*"",'[" & fich & "]" & Left(NomSht, 31) & "'!A1:J200,10,0)"
ShtTdB.Cells(LigSel, 10) = "=VLOOKUP(""*HT*"",'[" & fich & "]" & Left(NomSht, 31) & "'!A1:J200,10,0)"
.Parent.Close False 'indispensable...
End With
Next i
End Sub