May 05 2022 12:09 PM
May 05 2022 12:09 PM
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!
May 05 2022 12:50 PM
What is the code you're using now?
May 05 2022 01:00 PM
May 05 2022 01:20 PM
Because you use EntireRow, the entire row gets moved.
Which column or columns in the row would you like to move?
May 06 2022 07:37 AM - edited May 06 2022 07:43 AM
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
May 06 2022 12:08 PM
May 06 2022 12:12 PM
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.
May 06 2022 12:33 PM
You'll have to share the workbook - I now get Access Denied.
May 06 2022 12:52 PM
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.
Jun 06 2022 12:18 PM
Jun 06 2022 12:52 PM
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.
Jun 07 2022 01:04 PM
@Hans Vogelaar Now it's disappeared all together. Can I send it back to you?
Jun 07 2022 03:15 PM
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.
Jun 07 2022 03:35 PM
Jun 08 2022 12:46 AM
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