Hi ! Why if insert new raw, i have error?
Hi ! Why if insert new raw, i have error?
Because here the Target is not a single cell. It's a range of cells.
So, change your current code like this...
Also use with statement for sh to avoid calling it many times (like the below)![]()
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name <> "Master" Then If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub If Sh.Cells(Target.Row, "B").Value = "x" Then Sheets("Master").Range("ZZ1") = Sheets("Master").Range("ZZ1") + 1 Target.Offset(0, -1) = Sheets("Master").Range("ZZ1") Target.EntireRow.Copy Sheets("Master").Cells(Sheets("Master").Rows.Count, "A").End(xlUp).Offset(1, 0) ElseIf Sh.Cells(Target.Row, "B").Value = "y" And Sh.Cells(Target.Row, "A").Value <> "" Then Sh.Cells(Target.Row, "A").Value = Sh.Cells(Target.Row - 1, "A").Value + 0.1 Sh.Cells(Target.Row, "B").EntireRow.Copy Sheets("Master").Cells(Sheets("Master").Rows.Count, "A").End(xlUp).Offset(1, 0) End If End If End Sub
![]()
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) With Sh If .Name <> "Master" Then If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub If .Cells(Target.Row, "B").Value = "x" Then Sheets("Master").Range("ZZ1") = Sheets("Master").Range("ZZ1") + 1 Target.Offset(0, -1) = Sheets("Master").Range("ZZ1") Target.EntireRow.Copy Sheets("Master").Cells(Sheets("Master").Rows.Count, "A").End(xlUp).Offset(1, 0) ElseIf Sh.Cells(Target.Row, "B").Value = "y" And Sh.Cells(Target.Row, "A").Value <> "" Then .Cells(Target.Row, "A").Value = .Cells(Target.Row - 1, "A").Value + 0.1 .Cells(Target.Row, "B").EntireRow.Copy Sheets("Master").Cells(Sheets("Master").Rows.Count, "A").End(xlUp).Offset(1, 0) End If End If End With End Sub
Last edited by :) Sixthsense :); 01-08-2019 at 02:53 AM.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
thank you very much
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks