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
| Private Sub CommandButton2_Click()
Dim NewSh As Worksheet
Dim LastSh As Worksheet
Dim Line As Long
Set NewSh = ActiveWorkbook.Worksheets(Sheets.Count)
If Sheets.Count > 1 Then
Set LastSh = ActiveWorkbook.Worksheets(Sheets.Count - 1)
LastSh.Copy After:=ActiveWorkbook.Sheets(Sheets.Count)
Else
Set LastSh = ActiveWorkbook.Worksheets("Main")
ActiveSheet.Copy After:=ActiveWorkbook.Sheets(Sheets.Count)
End If
NewSh.Select
Select Case True
Case ComboBox1.Value = "EAC"
NewSh.Name = "EAC" & Sheets.Count - 1
NewSh.Range("Z4").Value = "EAC"
Case ComboBox1.Value = "AC"
NewSh.Name = "AC" & Sheets.Count - 1
NewSh.Range("Z4").Value = "AC"
End Select
NewSh.Select
On Error Resume Next
Range("G12:N263").Cells.SpecialCells(xlCellTypeConstants).ClearContents
Range("X9:AC10").Cells.SpecialCells(xlCellTypeConstants).ClearContents
Range("R12:R263").Cells.SpecialCells(xlCellTypeConstants).ClearContents
Range("T12:T263").Cells.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
Line = ActiveSheet.Cells(Rows.Count, "V").End(xlUp).Row + 1
ActiveSheet.Rows(Line).Insert xlShiftDown
ActiveSheet.Cells(Rows.Count - 1, "V").End(xlUp).Offset(1, 0).FormulaLocal = "=" & LastSh.Name & "!" & LastSh.Cells(6, "B").Address
ActiveSheet.Cells(Rows.Count - 1, "W").End(xlUp).Offset(1, 0).FormulaLocal = "=" & LastSh.Name & "!" & LastSh.Cells(4, "Z").Address
ActiveSheet.Cells(Rows.Count - 1, "X").End(xlUp).Offset(1, 0).FormulaLocal = "=" & LastSh.Name & "!" & LastSh.Cells(266, "E").Address
ActiveSheet.Cells(Rows.Count - 1, "Y").End(xlUp).Offset(1, 0).FormulaLocal = "=" & LastSh.Name & "!" & LastSh.Cells(266, "P").Address
ActiveSheet.Cells(Rows.Count - 1, "Z").End(xlUp).Offset(1, 0).FormulaLocal = "=" & LastSh.Name & "!" & LastSh.Cells(266, "W").Address
ActiveSheet.Cells(Rows.Count, "V").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(6, "B").Address
ActiveSheet.Cells(Rows.Count, "W").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(4, "Z").Address
ActiveSheet.Cells(Rows.Count, "X").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(266, "E").Address
ActiveSheet.Cells(Rows.Count, "Y").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(266, "P").Address
ActiveSheet.Cells(Rows.Count, "Z").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(266, "W").Address
If ActiveSheet.Range("W" & Line).Value = "AC" Then
ActiveSheet.Cells(Rows.Count, "AA").End(xlUp).Offset(1, 0).Formula = "=AA" & Line - 1 & "+X" & Line
ActiveSheet.Cells(Rows.Count, "AB").End(xlUp).Offset(1, 0).FormulaLocal = "=AB" & Line - 1 & "+Y" & Line
End If
If ActiveSheet.Range("W" & Line).Value = "EAC" Then
ActiveSheet.Cells(Rows.Count, "AA").End(xlUp).Offset(1, 0).FormulaLocal = "=AA" & Line - 1
ActiveSheet.Cells(Rows.Count, "AB").End(xlUp).Offset(1, 0).FormulaLocal = "=AB" & Line - 1 & "+Y" & Line
End If
ActiveSheet.Cells(Rows.Count, "AC").End(xlUp).Offset(1, 0).Select
Selection.Style = "percent"
ActiveSheet.Cells(Rows.Count, "AC").End(xlUp).Offset(1, 0).FormulaLocal = "=1-(AB" & Line & "/AA" & Line & ")"
Set NewSh = Nothing
Set LastSh = Nothing
End Sub |
Partager