I currently have a code which cuts and move a row based on a cell value to another sheet. Which is:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim raDest As Range
Dim raCell As Range
If Not Intersect(Target, Range("Y:Y")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then
Exit Sub
Else
If Target.Value = "Yes" Then
Application.ScreenUpdating = False
With ThisWorkbook.Sheets("Accounts 2021")
Set raDest = .Cells(.Rows.Count, "Y").End(xlUp).Offset(1, 0).EntireRow
End With
Me.Rows(Target.Row).Copy Destination:=raDest
Set raCell = raDest.Cells(1, "J")
If IsDate(raCell.Value) Then
raCell.Value = DateAdd("yyyy", 1, raCell.Value)
End If
Set raCell = raDest.Cells(1, "K")
If IsDate(raCell.Value) Then
raCell.Value = DateAdd("yyyy", 1, raCell.Value)
End If
raDest.Columns("M:U").ClearContents
Application.EnableEvents = False
Me.Rows(Target.Row).Delete
Application.EnableEvents = True
End If
Application.ScreenUpdating = True
End If
End If
End Sub
But I'm now trying to do the same thing, but not moving the cut row to another sheet, but instead having this cut row move to the bottom of the sheet.
Can this be done?
Thanks
Private Sub Worksheet_Change(ByVal Target As Range)
Dim raDest As Range
Dim raCell As Range
If Not Intersect(Target, Range("Y:Y")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then
Exit Sub
Else
If Target.Value = "Yes" Then
Application.ScreenUpdating = False
With ThisWorkbook.Sheets("Accounts 2021")
Set raDest = .Cells(.Rows.Count, "Y").End(xlUp).Offset(1, 0).EntireRow
End With
Me.Rows(Target.Row).Copy Destination:=raDest
Set raCell = raDest.Cells(1, "J")
If IsDate(raCell.Value) Then
raCell.Value = DateAdd("yyyy", 1, raCell.Value)
End If
Set raCell = raDest.Cells(1, "K")
If IsDate(raCell.Value) Then
raCell.Value = DateAdd("yyyy", 1, raCell.Value)
End If
raDest.Columns("M:U").ClearContents
Application.EnableEvents = False
Me.Rows(Target.Row).Delete
Application.EnableEvents = True
End If
Application.ScreenUpdating = True
End If
End If
End Sub
But I'm now trying to do the same thing, but not moving the cut row to another sheet, but instead having this cut row move to the bottom of the sheet.
Can this be done?
Thanks