Transfer edited rows only to new worksheet

akie91 

To transfer only the edited or new rows from one worksheet to another in Excel, you will indeed need to use VBA (Visual Basic for Applications) because formulas alone cannot detect changes in a worksheet.

Here is a basic outline of how you could accomplish this using VBA:

1. Create a Button: You can add a button to your worksheet that, when clicked, triggers the VBA code to transfer the edited or new rows.
2. VBA Code: Write VBA code that will compare the data between the current day's worksheet and the previous day's worksheet (or any other reference point). Identify the rows that have been edited or are new and copy them to the destination worksheet.

Here is a simplified example of what the VBA code might look like:

Vba Code is untested please backup your file.

Sub TransferEditedRows()
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim lastRowSource As Long
    Dim lastRowDest As Long
    Dim i As Long, j As Long
    
    ' Set source and destination worksheets
    Set wsSource = ThisWorkbook.Sheets("SourceSheet")
    Set wsDest = ThisWorkbook.Sheets("DestinationSheet")
    
    ' Find the last row with data in source and destination sheets
    lastRowSource = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    lastRowDest = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each row in the source sheet
    For i = 2 To lastRowSource ' Assuming data starts from row 2, adjust as needed
        Dim foundMatch As Boolean
        foundMatch = False
        
        ' Loop through each row in the destination sheet
        For j = 2 To lastRowDest
            ' Compare data in source and destination sheets
            If wsSource.Cells(i, "A").Value = wsDest.Cells(j, "A").Value Then ' Assuming employee ID is in column A
                foundMatch = True
                Exit For
            End If
        Next j
        
        ' If no match is found, copy the row to the destination sheet
        If Not foundMatch Then
            wsSource.Rows(i).Copy wsDest.Rows(lastRowDest + 1)
            lastRowDest = lastRowDest + 1
        End If
    Next i
End Sub

This code assumes that you have two sheets named "SourceSheet" and "DestinationSheet" where the data is located. Adjust the sheet names and column references according to your actual setup.

You can assign this macro to a button on your worksheet or run it manually from the VBA editor. Before using this code, make sure to save your workbook as a macro-enabled file (.xlsm). Additionally, you may need to further customize the code to match your specific data structure and requirements. The text, steps and the code were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.