VBA copy updates to new sheet

[floggingmolly]

I have a worksheet with columns A - AC. Column AC will be updated with "Red Flag" if criteria is not met. What I'm trying to do is copy any rows that were updated with Red Flag to a new sheet. Column E is the individuals ID #, so I was trying to copy based off columns E and AC. I'm not sure if this is possible. I want to add any new Red Flags to the end of the list on the 2nd sheet. Below is the code I tried but it's giving a 400 error. Any ideas?

Sub copyData_multiRef()

    Dim tWB As Workbook 'Thisworkbook
    Set tWB = ThisWorkbook
    
    Dim multiSH As Worksheet 'Thisworkbook MultiCol Sheet
    Set multiSH = tWB.Sheets("Notes")
    
    Dim filePath As String
    filePath = Application.GetOpenFilename
    
    Dim dataWB As Workbook 'Sample Data Workbook
    Set dataWB = Workbooks.Open(filePath)
    
    Dim dataSH As Worksheet 'Sample Data workbook ALLCOLUMN sheet
    Set dataSH = dataWB.Sheets("Red Flags")
    
    Dim a As Long, b As Long
    'Loop thru all the sample data records
    For a = 2 To dataSH.Range("A1").CurrentRegion.Rows.Count
        
        'Check if the records exists in the primary tool
        Dim recordExists As Boolean
        recordExists = False
        For b = 2 To multiSH.Range("A2").CurrentRegion.Rows.Count
            If dataSH.Range("E" & a).Value = multiSH.Range("E" & b).Value And _
                dataSH.Range("CA" & a).Value = multiSH.Range("CA" & b).Value Then
                recordExists = True
                Exit For
            End If
        Next b
        
        'Copy Data if not exists
        'Get the next avaialble row in the primary tool
        Dim nextRow As Long
        nextRow = multiSH.Range("A2").CurrentRegion.Rows.Count + 1
        If recordExists = False Then
            multiSH.Range("A:CA" & nextRow).Value = dataSH.Range("A:CA" & a).Value
        End If
    Next a
    
    'Close the Sample Data workbook
    dataWB.Close False
    
    MsgBox "Data copied successfully.", vbInformation, "VBA Copy New Data"
    
End Sub

 

[dashiellx]

Once you have identified the row you want to copy, something like this should work:

Dim sourceRow As Range
Dim targetWorksheet As Worksheet
Dim targetRow As Range
    
' Set the source row as the active row
Set sourceRow = ActiveSheet.Rows(ActiveCell.Row)
    
' Set the target worksheet where you want to copy the row
Set targetWorksheet = ThisWorkbook.Worksheets("TargetSheetName")
    
' Find the next available row in the target worksheet
Set targetRow = targetWorksheet.Cells(targetWorksheet.Rows.Count, "A").End(xlUp).Offset(1)

' Copy the values and formats from the source row to the target row
sourceRow.Copy Destination:=targetRow

' Clear the clipboard
Application.CutCopyMode = False