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
| Private Sub CommandButton25_Click()
Dim NewSh As Worksheet
Dim Ligne As Long
Worksheets("Add").Copy After:=ActiveWorkbook.Sheets(Sheets.Count)
Set NewSh = ActiveWorkbook.Worksheets(Sheets.Count)
NewSh.Name = "Add" & Sheets.Count - 3
NewSh.visible = True
Line = Sheets("Main").Cells(Rows.Count, "G").End(xlUp).Row + 1
Sheets("Main").Rows(Line).Insert xlShiftDown
Worksheets("Main").Cells(Rows.Count, "G").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(5, "C").Address
Worksheets("Main").Cells(Rows.Count, "H").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(4, "J").Address
Worksheets("Main").Cells(Rows.Count, "I").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(266, "F").Address
Worksheets("Main").Cells(Rows.Count, "J").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(266, "Q").Address
Worksheets("Main").Cells(Rows.Count, "K").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(266, "R").Address
i = Worksheets("Main").Cells(Rows.Count, "I").End(xlUp).Offset(1, 0).FormulaLocal
j = Worksheets("Main").Cells(Rows.Count, "J").End(xlUp).Offset(1, 0).FormulaLocal
Select Case True
Case Worksheets("Main").Cells(Rows.Count, "H").End(xlUp).Value = "AC"
Worksheets("Main").Cells(Rows.Count, "L").End(xlUp).Offset(1, 0).FormulaLocal = "=L277 + I" & i
Case Worksheets("Main").Cells(Rows.Count, "H").End(xlUp).Value = "EAC"
Worksheets("Main").Cells(Rows.Count, "M").End(xlUp).Offset(1, 0).FormulaLocal = "=M277 + J" & j
End Select
Worksheets("Main").Cells(Rows.Count, "N").End(xlUp).Offset(1, 0).FormulaLocal = "=1-(M" & j & "/L" & i & ")"
Worksheets("Main").Select
End Sub |
Partager