NeuroAgent

Excel VBA: Fix Macro Only Moving One Row

Excel VBA macro only moves last row to Closed Items sheet? Learn how to fix your code to properly accumulate all closed items in a complete list.

#data-management#excel-automation#excel-formulas#excel-vba#row-movement#vba-macros#vba-programming
Question

Excel VBA Macro Issue: MoveRowsTo Module Only Moving to One Row

I have a task list in Excel where I need to move rows from an “Open Items” sheet to a “Closed Items” sheet when column G is marked as “Closed”. My VBA macro works, but it’s only showing the most recently closed item in row 2 of the target sheet, rather than maintaining a list of all closed items.

Current Code:

vba
Sub MoveRowsToClosedItems()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim i As Long

' Set the source and target sheets
Set sourceSheet = ThisWorkbook.Worksheets("Open Items")
Set targetSheet = ThisWorkbook.Worksheets("Closed Items")

' Find the last row in the source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "G").End(xlUp).Row

' Loop through each row in the source sheet
For i = 2 To lastRow
  ' Check if cell in column G contains "Closed"
  If sourceSheet.Cells(i, "G").Value = "Closed" Then
    ' Copy the entire row to the target sheet
    sourceSheet.Rows(i).Copy Destination:=targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1)
    ' Delete the row from the source sheet
    sourceSheet.Rows(i).Delete
    ' Decrement the loop counter as the rows are shifting up
    i = i - 1
    ' Update the last row value
    lastRow = lastRow - 1
  End If
Next i
End Sub

Problem:

When I run this macro, it successfully moves rows from the “Open Items” sheet to the “Closed Items” sheet, but only the most recently closed item appears in the target sheet. I need all closed items to be listed in the target sheet.

Desired Outcome:

I want the macro to maintain a complete list of all closed items in the “Closed Items” sheet, with each new closed item being added to the bottom of the list rather than replacing previous entries.

How can I modify my VBA code to ensure all closed items are properly accumulated in the target sheet?

NeuroAgent

When moving rows from one sheet to another in Excel VBA, the issue of only the last row appearing typically stems from incorrect last row detection or improper row deletion during the loop. Your code is on the right track, but there are several improvements needed to ensure all closed items are properly accumulated in the target sheet.

The main issues in your current code are:

  • The last row detection in the target sheet may not work correctly if the sheet is empty or has formatting issues
  • Deleting rows during the loop can cause unexpected behavior
  • The copying mechanism may not be appending correctly

Contents

Improved VBA Solution

Here’s a modified version of your code that addresses the issues:

vba
Sub MoveRowsToClosedItems()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim targetLastRow As Long
    Dim i As Long
    
    ' Turn off screen updating for better performance
    Application.ScreenUpdating = False
    
    ' Set the source and target sheets
    Set sourceSheet = ThisWorkbook.Worksheets("Open Items")
    Set targetSheet = ThisWorkbook.Worksheets("Closed Items")
    
    ' Find the last row in the source sheet
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "G").End(xlUp).Row
    
    ' Find the last row in the target sheet (more reliable method)
    On Error Resume Next
    targetLastRow = targetSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If Err.Number <> 0 Then
        targetLastRow = 1 ' If no data found, start from row 1
    End If
    On Error GoTo 0
    
    ' Loop through each row in the source sheet (bottom to top to avoid shifting issues)
    For i = lastRow To 2 Step -1
        ' Check if cell in column G contains "Closed"
        If sourceSheet.Cells(i, "G").Value = "Closed" Then
            ' Copy the entire row to the target sheet
            sourceSheet.Rows(i).Copy Destination:=targetSheet.Rows(targetLastRow + 1)
            
            ' Increment target last row
            targetLastRow = targetLastRow + 1
            
            ' Delete the row from the source sheet
            sourceSheet.Rows(i).Delete Shift:=xlUp
        End If
    Next i
    
    ' Turn screen updating back on
    Application.ScreenUpdating = True
    
    MsgBox "Rows moved successfully!", vbInformation
End Sub

Alternative Approach Without Row Deletion

For better performance and reliability, consider this approach that doesn’t delete rows immediately:

vba
Sub MoveRowsToClosedItemsAlternative()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim targetLastRow As Long
    Dim i As Long
    Dim rowsToDelete As Collection
    Dim rowToDelete As Variant
    
    ' Turn off screen updating for better performance
    Application.ScreenUpdating = False
    
    ' Set the source and target sheets
    Set sourceSheet = ThisWorkbook.Worksheets("Open Items")
    Set targetSheet = ThisWorkbook.Worksheets("Closed Items")
    
    ' Initialize collection to store rows to delete
    Set rowsToDelete = New Collection
    
    ' Find the last row in the source sheet
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "G").End(xlUp).Row
    
    ' Find the last row in the target sheet
    On Error Resume Next
    targetLastRow = targetSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If Err.Number <> 0 Then
        targetLastRow = 1
    End If
    On Error GoTo 0
    
    ' First pass: identify and copy rows to move
    For i = 2 To lastRow
        If sourceSheet.Cells(i, "G").Value = "Closed" Then
            ' Add row index to collection (store as string to avoid type issues)
            rowsToDelete.Add i
            
            ' Copy the row to target sheet
            sourceSheet.Rows(i).Copy Destination:=targetSheet.Rows(targetLastRow + 1)
            targetLastRow = targetLastRow + 1
        End If
    Next i
    
    ' Second pass: delete rows from source (in reverse order to avoid shifting issues)
    For Each rowToDelete In rowsToDelete
        sourceSheet.Rows(rowToDelete).Delete Shift:=xlUp
    Next rowToDelete
    
    ' Turn screen updating back on
    Application.ScreenUpdating = True
    
    MsgBox "Successfully moved " & rowsToDelete.Count & " rows to Closed Items!", vbInformation
End Sub

Debugging and Error Handling

If you’re still experiencing issues, add debugging to help identify the problem:

vba
Sub MoveRowsWithDebug()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim targetLastRow As Long
    Dim i As Long
    Dim movedCount As Long
    
    movedCount = 0
    
    ' Set the source and target sheets
    Set sourceSheet = ThisWorkbook.Worksheets("Open Items")
    Set targetSheet = ThisWorkbook.Worksheets("Closed Items")
    
    ' Debug information
    Debug.Print "Starting row movement..."
    Debug.Print "Source sheet last row: " & sourceSheet.Cells(sourceSheet.Rows.Count, "G").End(xlUp).Row
    
    ' Find the last row in the target sheet
    On Error Resume Next
    targetLastRow = targetSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If Err.Number <> 0 Then
        targetLastRow = 1
        Debug.Print "Target sheet is empty, starting from row 1"
    Else
        Debug.Print "Target sheet last row: " & targetLastRow
    End If
    On Error GoTo 0
    
    ' Loop through each row in the source sheet (bottom to top)
    For i = sourceSheet.Cells(sourceSheet.Rows.Count, "G").End(xlUp).Row To 2 Step -1
        Debug.Print "Checking row " & i & ", value in G: " & sourceSheet.Cells(i, "G").Value
        
        If sourceSheet.Cells(i, "G").Value = "Closed" Then
            ' Copy the entire row to the target sheet
            targetSheet.Rows(targetLastRow + 1).Value = sourceSheet.Rows(i).Value
            
            ' Increment counter
            movedCount = movedCount + 1
            targetLastRow = targetLastRow + 1
            
            Debug.Print "Moved row " & i & " to target row " & targetLastRow
            
            ' Delete the row from the source sheet
            sourceSheet.Rows(i).Delete Shift:=xlUp
        End If
    Next i
    
    Debug.Print "Total rows moved: " & movedCount
    MsgBox "Moved " & movedCount & " rows successfully!", vbInformation
End Sub

Best Practices for Row Movement

When working with row movement in Excel VBA, follow these best practices:

  1. Loop backwards: Always loop from bottom to top when deleting rows to avoid shifting issues.
  2. Use reliable last row detection: The Find method is more reliable than End(xlUp) for detecting the last row.
  3. Turn off screen updating: This improves performance significantly for large datasets.
  4. Use collections for complex operations: When you need to track multiple rows, use a collection.
  5. Consider using values instead of copy: For better performance, copy values rather than formatting.
  6. Add error handling: Include error handling to prevent crashes if sheets don’t exist.

Complete Working Solution

Here’s the most robust solution that combines all the best practices:

vba
Sub MoveRowsToClosedItemsFinal()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim targetLastRow As Long
    Dim i As Long
    Dim movedCount As Long
    
    On Error GoTo ErrorHandler
    
    ' Turn off screen updating for better performance
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    movedCount = 0
    
    ' Set the source and target sheets with error handling
    Set sourceSheet = ThisWorkbook.Worksheets("Open Items")
    Set targetSheet = ThisWorkbook.Worksheets("Closed Items")
    
    ' Find the last row in the source sheet
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "G").End(xlUp).Row
    If lastRow < 2 Then
        MsgBox "No data found in Open Items sheet", vbExclamation
        Exit Sub
    End If
    
    ' Find the last row in the target sheet
    On Error Resume Next
    targetLastRow = targetSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If Err.Number <> 0 Then
        targetLastRow = 1 ' If no data found, start from row 1
    End If
    On Error GoTo 0
    
    ' Loop through each row in the source sheet (bottom to top)
    For i = lastRow To 2 Step -1
        ' Check if cell in column G contains "Closed"
        If sourceSheet.Cells(i, "G").Value = "Closed" Then
            ' Copy only values (faster and avoids formatting issues)
            targetSheet.Rows(targetLastRow + 1).Value = sourceSheet.Rows(i).Value
            
            ' Increment counter and target last row
            movedCount = movedCount + 1
            targetLastRow = targetLastRow + 1
            
            ' Delete the row from the source sheet
            sourceSheet.Rows(i).Delete Shift:=xlUp
        End If
    Next i
    
    ' Restore Excel settings
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
    MsgBox "Successfully moved " & movedCount & " rows to Closed Items!", vbInformation
    Exit Sub
    
ErrorHandler:
    ' Restore Excel settings in case of error
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
    MsgBox "Error occurred: " & Err.Description, vbCritical
End Sub

This final solution addresses all the common issues with row movement in Excel VBA and should reliably move all closed items to your target sheet while maintaining a complete list of all closed items.

How to optimize VBA performance when moving large datasets in Excel?What's the best way to handle errors in Excel VBA row movement macros?How can I modify this macro to move rows based on multiple criteria?What are the best practices for maintaining data integrity when moving rows in Excel VBA?How to create a log of moved items in Excel VBA row movement?Can this macro be adapted to work with filtered data in Excel?
Ask NeuroAgent