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
|
Private Sub CommandButton2_Click()
pmcopie = Application.WorksheetFunction.VLookup(Range("B3").Value, Sheets("Liste").Range("A2:L51"), 9, 0)
pmdate = Application.WorksheetFunction.VLookup(Range("B3").Value, Sheets("Liste").Range("A2:L51"), 11, 0)
pmjustif = Application.WorksheetFunction.VLookup(Range("B3").Value, Sheets("Liste").Range("A2:L51"), 12, 0)
pment = Application.WorksheetFunction.VLookup(Range("B3").Value, Sheets("Liste").Range("A2:L51"), 2, 0)
If pmcopie = "o" Then
Monfichier = Application.WorksheetFunction.VLookup(Range("B3").Value, Sheets("Liste").Range("A2:L51"), 10, 0)
Rep = "Z:\Suivi dossiers PM-LS\Dossiers PP\"
Set wb = Workbooks.Open(Filename:=Rep & Monfichier)
If wb.ReadOnly Then
MsgBox "Fichier" & Monfichier & " déjà ouvert !"
wb.Close False
Else
Dim NewLig As Long
Dim Sh As Worksheet
Set Sh = Workbooks("Teste Pochette dossier PM.xlsm").Sheets("Mutual.")
With Workbooks(Monfichier).Sheets("2011c")
NewLig = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
If NewLig < 20 Then NewLig = 20
If pment = "101" Then
.Range("A" & NewLig).Value = Sh.Range("F1").Value
.Range("B" & NewLig).Value = Sh.Range("C10").Value
.Range("C" & NewLig).Value = IIf(Val(Sh.Range("B13").Value) <> 0, Sh.Range("B13").Value, "")
.Range("D" & NewLig).Value = IIf(Val(Sh.Range("B14").Value) <> 0, Sh.Range("B14").Value, "")
.Range("E" & NewLig).Value = IIf(Val(Sh.Range("D19").Value) <> 0, Sh.Range("D19").Value, "")
.Range("G" & NewLig).Value = IIf(Val(Sh.Range("E19").Value) <> 0, Sh.Range("E19").Value, "")
.Range("K" & NewLig).Value = IIf(Val(Sh.Range("G19").Value) <> 0, Sh.Range("G19").Value, "")
.Range("L" & NewLig).Value = IIf(Val(Sh.Range("F19").Value) <> 0, Sh.Range("F19").Value, "")
.Range("M" & NewLig).Value = IIf((Sh.Range("H33").Value) = "x" Or (Sh.Range("H33").Value) = "X", "F", Workbooks(Monfichier).Sheets("2011c").Range("M" & NewLig).Value)
.Range("M" & NewLig).Value = IIf((Sh.Range("H34").Value) = "x" Or (Sh.Range("H34").Value) = "X", "R", Workbooks(Monfichier).Sheets("2011c").Range("M" & NewLig).Value)
.Range("M" & NewLig).Value = IIf((Sh.Range("H35").Value) = "x" Or (Sh.Range("H35").Value) = "X", "E", Workbooks(Monfichier).Sheets("2011c").Range("M" & NewLig).Value)
.Range("S" & NewLig).Value = "Accepté"
.Range("U" & NewLig).Value = IIf((Sh.Range("D36").Value) = "x" Or (Sh.Range("D36").Value) = "X", "", "x")
.Range("V" & NewLig).Value = IIf((Sh.Range("D35").Value) = "x" Or (Sh.Range("D35").Value) = "X", "", "x")
.Range("X" & NewLig).Value = IIf((Sh.Range("D37").Value) = "x" Or (Sh.Range("D37").Value) = "X", "", "x")
.Range("Y" & NewLig).Value = IIf((Sh.Range("D38").Value) = "x" Or (Sh.Range("D38").Value) = "X", "", "x")
ElseIf pment <> "101" And pmdate = "n" And pmjustif = "0" Then
.Range("A" & NewLig).Value = Sh.Range("F1").Value
.Range("B" & NewLig).Value = Sh.Range("C10").Value
.Range("C" & NewLig).Value = IIf(Val(Sh.Range("D19").Value) <> 0, Sh.Range("D19").Value, "")
.Range("E" & NewLig).Value = IIf(Val(Sh.Range("E19").Value) <> 0, Sh.Range("E19").Value, "")
.Range("I" & NewLig).Value = IIf(Val(Sh.Range("G19").Value) <> 0, Sh.Range("G19").Value, "")
.Range("J" & NewLig).Value = IIf(Val(Sh.Range("F19").Value) <> 0, Sh.Range("F19").Value, "")
.Range("K" & NewLig).Value = IIf((Sh.Range("H33").Value) = "x" Or (Sh.Range("H33").Value) = "X", "F", Workbooks(Monfichier).Sheets("2011c").Range("M" & NewLig).Value)
.Range("K" & NewLig).Value = IIf((Sh.Range("H34").Value) = "x" Or (Sh.Range("H34").Value) = "X", "R", Workbooks(Monfichier).Sheets("2011c").Range("M" & NewLig).Value)
.Range("K" & NewLig).Value = IIf((Sh.Range("H35").Value) = "x" Or (Sh.Range("H35").Value) = "X", "E", Workbooks(Monfichier).Sheets("2011c").Range("M" & NewLig).Value)
.Range("N" & NewLig).Value = "Accepté"
.Range("P" & NewLig).Value = IIf((Sh.Range("D36").Value) = "x" Or (Sh.Range("D36").Value) = "X", "", "x")
.Range("Q" & NewLig).Value = IIf((Sh.Range("D35").Value) = "x" Or (Sh.Range("D35").Value) = "X", "", "x")
.Range("S" & NewLig).Value = IIf((Sh.Range("D37").Value) = "x" Or (Sh.Range("D37").Value) = "X", "", "x")
.Range("T" & NewLig).Value = IIf((Sh.Range("D38").Value) = "x" Or (Sh.Range("D38").Value) = "X", "", "x")
Else
.Range("A" & NewLig).Value = Sh.Range("F1").Value
.Range("B" & NewLig).Value = Sh.Range("C10").Value
.Range("C" & NewLig).Value = IIf(Val(Sh.Range("D19").Value) <> 0, Sh.Range("D19").Value, "")
.Range("E" & NewLig).Value = IIf(Val(Sh.Range("E19").Value) <> 0, Sh.Range("E19").Value, "")
.Range("I" & NewLig).Value = IIf(Val(Sh.Range("G19").Value) <> 0, Sh.Range("G19").Value, "")
.Range("J" & NewLig).Value = IIf(Val(Sh.Range("F19").Value) <> 0, Sh.Range("F19").Value, "")
.Range("K" & NewLig).Value = IIf((Sh.Range("H33").Value) = "x" Or (Sh.Range("H33").Value) = "X", "F", Workbooks(Monfichier).Sheets("2011c").Range("M" & NewLig).Value)
.Range("K" & NewLig).Value = IIf((Sh.Range("H34").Value) = "x" Or (Sh.Range("H34").Value) = "X", "R", Workbooks(Monfichier).Sheets("2011c").Range("M" & NewLig).Value)
.Range("K" & NewLig).Value = IIf((Sh.Range("H35").Value) = "x" Or (Sh.Range("H35").Value) = "X", "E", Workbooks(Monfichier).Sheets("2011c").Range("M" & NewLig).Value)
.Range("N" & NewLig).Value = "Accepté"
End If
End With
wb.Save
wb.Close
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="infotec ISC 4045 RPCS"
Workbooks("Teste Pochette dossier PM.xlsm").Sheets("Mutual.").Range("F1").MergeArea.ClearContents
End If
MsgBox "Clic encore une fois le fichier était ouvert !"
ElseIf pmcopie = "n" Then
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="infotec ISC 4045 RPCS"
Workbooks("Teste Pochette dossier PM.xlsm").Sheets("Mutual.").Range("F1").MergeArea.ClearContents
End If
Set Sh = Nothing
Set wb = Nothing
End Sub |
Partager