VBA formula help

Question

Hi,

I'm hoping someone is able to help please.

Basically I want to be able to entire rows from one sheet to another based on what is entered into one cell of the row.

For the attached document, every time Yes is entered into column N, i'd want that row to move over to the 'signed off' worksheet.

This document is regularly added to so will need to continue moving over as new rows are added.

Thanks

Answer 1

Hi Jen_993,

Welcome to Microsoft Community.

Right-click the worksheet name you want to automatically migrate (e.g., Sheet1) > View Code.

Paste the following code into the code editing area. Save the workbook as an Excel Macro-Enabled Workbook.

Private Sub Worksheet_Change(ByVal Target As Range) 

    Dim wsSource As Worksheet 

    Dim wsTarget As Worksheet 

    Dim lastRow As Long 

    Dim targetRow As Long 

    Dim colToWatch As String 

    Dim foundLastCell As Range 

    Set wsSource = Me 

    Set wsTarget = ThisWorkbook.Worksheets("signed off") 

    colToWatch = "N" 

    If Not Intersect(Target, Me.Columns(colToWatch)) Is Nothing Then 

        If Target.Cells.count = 1 Then 

            If Target.Value = "Yes" Then 

                Application.EnableEvents = False 

                On Error Resume Next 

                Set foundLastCell = wsTarget.Cells.Find(What:="*", After:=wsTarget.Cells(1, 1), LookIn:=xlFormulas, _ 

                                                        SearchOrder:=xlByRows, SearchDirection:=xlPrevious) 

                On Error GoTo 0 

                If Not foundLastCell Is Nothing Then 

                    lastRow = foundLastCell.Row 

                Else 

                    lastRow = 0 

                End If 

                targetRow = lastRow + 1 

                Target.EntireRow.Cut Destination:=wsTarget.Rows(targetRow) 

                Application.EnableEvents = True 

                Application.CutCopyMode = False 

            End If 

        End If 

    End If 

End Sub

For demonstration, I entered "Yes" in cell N4 Sheet1,

and the script automatically moved the current row to the latest row in the "signed off" worksheet.

Hope the script works for you as well, If you have any other questions or want to share more context, please let me know in your reply!

Best Regards,

Thomas C - MSFT | Microsoft Community Support Specialist

Answer 2

There is no way to attach a file here, please use an Online File Hoster as described here:
Why a sample file is important for troubleshooting. How to do it. - Microsoft Community

Andreas.