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

[cak980]

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!  

[#2]

@cak980 

What is the code you're using now?

[#3]

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)
myCell.EntireRow.Delete
End If
Next
End Sub

[#4]

@cak980 

Thanks!

Because you use EntireRow, the entire row gets moved.

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

[#5]

@HansVogelaar  A-E

[#6]

@cak980 

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

[#7]

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

[#8]

@cak980 

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.

[#9]

@HansVogelaar  https://docs.google.com/spreadsheets/d/1a0Pfj0Msj6X03atXb-wYZnRB0yBuK_Nt/edit?usp=sharing&ouid=11491... 

[#10]

@cak980 

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

[#11]

https://docs.google.com/spreadsheets/d/1a0Pfj0Msj6X03atXb-wYZnRB0yBuK_Nt/edit?usp=sharing&ouid=11491...

[#12]

@cak980 

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.

[#13]

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!

[#14]

@cak980 

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.

[#15]

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

[#16]

@cak980 

Sure, go ahead.

[#17]

https://docs.google.com/spreadsheets/d/1QlW5A_IQAQOa2uSOh3ZiGVyocKk8rpra/edit?usp=sharing&ouid=11491...

[#18]

@cak980 

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.

[#19]

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

[#20]

@cak980 

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

to

    For s = m To 6 Step -1