Using VBA to move a range of data to a different sheet when complete (need the for dummies version)

Copper Contributor

I'm trying to setup a macro so that when a task is completed (indicated with X), it automatically gets moved to a separate tab and deleted from original location.  I was able to accomplish this but it moved the entire row.  I'm trying to move a range of data, not the entire row.  Thanks!  

25 Replies
Sub move_rows_to_another_sheet()
For Each myCell In Selection.Columns(4).Cells
If myCell.Value = "Closed" Then
myCell.EntireRow.Copy Worksheets("Sheet2").Range("A" & Rows.Count).End(3)(2)
End If
End Sub



Because you use EntireRow, the entire row gets moved.

Which column or columns in the row would you like to move?


Try this:


Sub move_rows_to_another_sheet()
    Dim myCell As Range
    Dim w As Worksheet
    Dim s As Long
    Dim m As Long
    Dim t As Long
    Dim rng As Range
    Set w = Worksheets("Sheet2")
    t = w.Range("A" & w.Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    m = Selection.Rows.Count
    For s = m To 1 Step -1
        If Selection.Range("D" & s).Value = "Closed" Then
            t = t + 1
            Set rng = Range("A" & Selection.Range("D" & s).Row).Resize(ColumnSize:=4)
            rng.Copy Destination:=w.Range("A" & t)
            rng.Delete Shift:=xlShiftUp
        End If
    Next s
    Application.ScreenUpdating = True
End Sub



I must be doing something wrong, it's not working. I made some edits - sheet 2 is actually "Completed" and "Closed" is actually "X" and is column E


Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.


You'll have to share the workbook - I now get Access Denied.


Thanks, that worked.

You copied the macro into a worksheet module. It should be in a standard module, created by selecting Insert > Module in the Visual Basic Editor.

You used COMPLETEDITEMS in the code, but the sheet is named COMPLETED ITEMS (with a space).

I changed the macro slightly so that you don't have to select a range. It now moves all rows with "x" in column E.

See the attached version.

Hans - I've made some changes to my worksheet but the macro doesn't work now. I get the message "ambiguous name detected". Also, I tried copying the macro you created to a different worksheet and when I did that, it would always open the original file as well. This was before I was getting the error message. Can you help? Thanks!


Do you have two copies of the macro in the same workbook? If so, delete one of them.


If you use the command button:

Right-click it and select Assign Macro... from the context menu.

Select the macro in the active workbook, then click OK.

@HansVogelaar Now it's disappeared all together.  Can I send it back to you?


Here is the workbook with the original macro, I haven't check whether it needs to be updated.

To view the macro, press Alt+F11 to activate the Visual Basic Editor.

Thank you! It starts at row 7 instead of 6 (first row). What's the edit to change that?


Don't you mean "It starts at row 6 instead of 7 (first row)"?

If so, change the line

    For s = m To 7 Step -1


    For s = m To 6 Step -1