Hello,
Below code breaks - if any rows are inserted between row 5 and row 20.
kindly assist in modfying below code.
Below code breaks - if any rows are inserted between row 5 and row 20.
kindly assist in modfying below code.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
ay = Target.Row
ax = Target.Column
If ax = 10 Then
Application.ScreenUpdating = False
Application.EnableEvents = False
With Sheets("Output")
Select Case ay
Case 5
x = WorksheetFunction.Match(Sheets("Output").Range("$J$5"), Sheets("Dropdown").Range("$G$1:$G$13"), 0) - 2
.Range(.Cells(6, 12 + x * 3), .Cells(19, 13 + x * 3)).Copy Destination:=.Range("$J$6")
Case 6 To 20
yy = WorksheetFunction.Sum(.Range("$J$6:$J$19"))
.Range("$K$6:$K$19").NumberFormat = "0%"
.Range("$j$6:$j$19").NumberFormat = "0"
For y = 6 To 19
.Cells(y, 11) = .Cells(y, 10) / yy
Next y
x = WorksheetFunction.Match(Sheets("Output").Range("$J$5"), Sheets("Dropdown").Range("$G$1:$G$13"), 0) - 2
.Cells(5, 12 + x * 3) = Sheets("Dropdown").Cells(2 + x, 7)
.Cells(5, 13 + x * 3) = .Cells(5, 12 + x * 3) & " Act"
.Range("$J$6:$K$19").Copy Destination:=.Cells(6, 12 + x * 3)
.Range("J20") = yy
.Cells(20, 12 + x * 3) = yy
End Select
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub