Cutting and pasting data based on a criteria

LesKing 

Your current macro is on the right track, but there are a few adjustments needed to make it work correctly. Here is a revised version of your macro:

Vba code is untested, please backup your file.

Sub MoveArchivedRecords()
    Dim wsDatabase As Worksheet
    Dim wsArchived As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim j As Long
    
    ' Set references to the Database and Archived Records worksheets
    Set wsDatabase = ThisWorkbook.Worksheets("Database")
    Set wsArchived = ThisWorkbook.Worksheets("Archived Records")
    
    ' Find the last row with data in column H of the Database worksheet
    lastRow = wsDatabase.Cells(wsDatabase.Rows.Count, "H").End(xlUp).Row
    
    ' Loop through each row in column H of the Database worksheet
    For i = lastRow To 3 Step -1 ' Start from the last row and loop backwards to avoid issues with row deletion
        
        ' Check if the value in column H is "Archived"
        If wsDatabase.Cells(i, "H").Value = "Archived" Then
            ' Find the next available row in the Archived Records worksheet
            j = wsArchived.Cells(wsArchived.Rows.Count, 1).End(xlUp).Row + 1
            
            ' Cut the entire row from the Database worksheet and paste it into the Archived Records worksheet
            wsDatabase.Rows(i).Cut Destination:=wsArchived.Rows(j)
            
            ' Delete the row from the Database worksheet
            wsDatabase.Rows(i).Delete
        End If
    Next i
    
    ' Activate the Archived Records worksheet and select the top-left cell
    wsArchived.Activate
    wsArchived.Cells(1, 1).Select
End Sub

Here's a breakdown of the changes and improvements made:

1. Declared variables to store references to the Database and Archived Records worksheets, as well as other variables used in the macro.
2. Used Set keyword to assign worksheet references.
3. Reversed the loop to start from the last row and loop backwards to avoid issues with row deletion.
4. Used explicit references to columns in the cells and rows manipulation to ensure clarity and avoid potential errors.
5. Updated the loop to correctly find the next available row in the Archived Records worksheet.
6. Corrected the syntax for cutting and pasting rows.
7. Activated the Archived Records worksheet and selected the top-left cell at the end of the macro.

These adjustments should resolve the issues you were facing with your macro. 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.