Home

VBA to Move rows in a sheet based on the project Status

%3CLINGO-SUB%20id%3D%22lingo-sub-186359%22%20slang%3D%22en-US%22%3EVBA%20to%20Move%20rows%20in%20a%20sheet%20based%20on%20the%20project%20Status%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-186359%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20on%20a%20spread%20sheet%20that%20is%20a%20list%20of%20projects.%20Basically%20once%20a%20project%20is%20completed%2C%20i%20want%20to%20move%20that%20entire%20row%20to%20a%20completed%20projects%20sheet%2C%20and%20delete%20the%20row%20from%20the%20sheet%20it's%20in.%20My%20Range%20is%20columns%20A-U.%20In%20column%20U%2C%20once%20the%20project%20is%20completed%20i%20want%20the%20row%20automatically%20moved.%20i%20have%20attached%20a%20screenshot.%20Thanks%20in%20advanced.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-186359%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-449365%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20to%20Move%20rows%20in%20a%20sheet%20based%20on%20the%20project%20Status%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-449365%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F37127%22%20target%3D%22_blank%22%3E%40Matt%20Mickle%3C%2FA%3E%26nbsp%3Bcan%20we%20send%20back%20from%20completed%20to%20project%20list%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-266929%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20to%20Move%20rows%20in%20a%20sheet%20based%20on%20the%20project%20Status%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-266929%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20this%20solution.%26nbsp%3B%20I%20was%20able%20to%20use%20it%20for%20my%20needs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20rows%20are%20also%20removed%20and%20sent%20to%20the%20completed%20sheet%20when%20I%20copy%20and%20paste%20seemingly%20unrelated%20cells%20to%20that%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20for%20a%20row%20which%20is%20not%20marked%20as%20%22complete%22%2C%20I%20paste%20a%20few%20cells%20from%20another%20sheet.%26nbsp%3B%20As%20soon%20as%20I%20paste%2C%20the%20row%20is%20moved.%26nbsp%3B%20The%20cells%20I%20am%20pasting%20to%20not%20include%20the%20cell%20I%20am%20using%20to%20mark%20it%20as%20%22Complete%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-189456%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20to%20Move%20rows%20in%20a%20sheet%20based%20on%20the%20project%20Status%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-189456%22%20slang%3D%22en-US%22%3E%3CP%3ETiffany-%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJust%20wanted%20to%20follow%20up%20and%20see%20if%20you%20were%20able%20to%20get%20your%20issue%20sorted%20out%3F%26nbsp%3B%20If%20you're%20still%20having%20trouble%20feel%20free%20to%20reach%20back%20out.%26nbsp%3B%20Always%20happy%20to%20help!%26nbsp%3B%20That's%20what%20the%20Excel%20Community%20is%20here%20for!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-187026%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20to%20Move%20rows%20in%20a%20sheet%20based%20on%20the%20project%20Status%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-187026%22%20slang%3D%22en-US%22%3E%3CP%3ETiffany-%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20have%20attached%20a%20sample%20workbook%20for%20your%20reference.%26nbsp%3B%20You%20will%20need%20to%20paste%20this%20code%20in%20the%20Project%20List%20worksheet%20code%20module%20in%20the%20attached%20file%20for%20it%20to%20work%20properly%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1.%20Alt%20%2B%20F11%20(Access%20VBE)%3C%2FP%3E%0A%3CP%3E2.%20Double%20click%20the%20icon%20in%20the%20Project%20Explorer%20that%20says%20Project%20List%3C%2FP%3E%0A%3CP%3E3.%20Paste%20the%20code%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20'If%20Cell%20that%20is%20edited%20is%20in%20column%20U%20and%20the%20value%20is%20completed%20then%0A%20%20%20%20If%20Target.Column%20%3D%2021%20And%20Target.Value%20%3D%20%22Completed%22%20Then%0A%20%20%20%20%20%20%20%20'Define%20last%20row%20on%20completed%20worksheet%20to%20know%20where%20to%20place%20the%20row%20of%20data%0A%20%20%20%20%20%20%20%20LrowCompleted%20%3D%20Sheets(%22Completed%22).Cells(Rows.Count%2C%20%22A%22).End(xlUp).Row%0A%20%20%20%20%20%20%20%20'Copy%20and%20paste%20data%0A%20%20%20%20%20%20%20%20Range(%22A%22%20%26amp%3B%20Target.Row%20%26amp%3B%20%22%3AU%22%20%26amp%3B%20Target.Row).Copy%20Sheets(%22Completed%22).Range(%22A%22%20%26amp%3B%20LrowCompleted%20%2B%201)%0A%20%20%20%20%20%20%20%20'Delete%20Row%20from%20Project%20List%0A%20%20%20%20%20%20%20%20Range(%22A%22%20%26amp%3B%20Target.Row%20%26amp%3B%20%22%3AU%22%20%26amp%3B%20Target.Row).Delete%20xlShiftUp%0A%20%20%20%20End%20If%0A%20%20%20%20Application.EnableEvents%20%3D%20True%0AEnd%20Sub%0A%0A%3C%2FPRE%3E%0A%3CP%3EPlease%20note%20that%20you%20may%20need%20to%20adjust%20the%20worksheet%20names%20in%20the%20VBA%20code%20based%20on%20your%20actual%20worksheet%20names.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Tiffany Gaquin
Visitor

I am working on a spread sheet that is a list of projects. Basically once a project is completed, i want to move that entire row to a completed projects sheet, and delete the row from the sheet it's in. My Range is columns A-U. In column U, once the project is completed i want the row automatically moved. i have attached a screenshot. Thanks in advanced.

 

 

4 Replies

Tiffany-

 

I have attached a sample workbook for your reference.  You will need to paste this code in the Project List worksheet code module in the attached file for it to work properly:

 

1. Alt + F11 (Access VBE)

2. Double click the icon in the Project Explorer that says Project List

3. Paste the code

 

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Application.EnableEvents = False
    'If Cell that is edited is in column U and the value is completed then
    If Target.Column = 21 And Target.Value = "Completed" Then
        'Define last row on completed worksheet to know where to place the row of data
        LrowCompleted = Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Row
        'Copy and paste data
        Range("A" & Target.Row & ":U" & Target.Row).Copy Sheets("Completed").Range("A" & LrowCompleted + 1)
        'Delete Row from Project List
        Range("A" & Target.Row & ":U" & Target.Row).Delete xlShiftUp
    End If
    Application.EnableEvents = True
End Sub

Please note that you may need to adjust the worksheet names in the VBA code based on your actual worksheet names.

 

Tiffany-

 

Just wanted to follow up and see if you were able to get your issue sorted out?  If you're still having trouble feel free to reach back out.  Always happy to help!  That's what the Excel Community is here for!

Thank you for this solution.  I was able to use it for my needs.

 

However, rows are also removed and sent to the completed sheet when I copy and paste seemingly unrelated cells to that row.

 

So, for a row which is not marked as "complete", I paste a few cells from another sheet.  As soon as I paste, the row is moved.  The cells I am pasting to not include the cell I am using to mark it as "Complete"

 

@Matt Mickle can we send back from completed to project list?

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies