VBA to Move Row in a Sheet Based on Cell Value

[samjbeattie]

Ok so previously someone on this forum found me a solution for this, but after making some changes to the spreadsheet, there appears to be further issues.

I want the VBA to do the following:

If for example the data in cell B10 on the tab 'TSP Milestone Tracker' reads 'Complete', the entire line (B10:L10) is to be moved (not copied), to another sheet named 'TSP Completed Milestones' and inserted into the B10:L10 location.

Following this, each further line of data marked 'complete' on 'TSP Milestone Tracker', needs then to be moved to the next available line on the 'TSP Completed Milestones' sheet below the previous line of data and all other lines on 'TSP Milestone Tracker' moved up to fill the gap.

I would also like to be able to add further lines to the data beyond line 179 on 'TSP Milestone Tracker' so the function must be flexible to allow this.

Hopefully this makes sense? The file has been attached.

If you need any further info. I can clarify.

Thanks in advance.

[harrisonland]

Not given this much thought, but you may be better converting the destination worksheet area to a table, then it'll auto-expand with new data without specific code to do it. You could also do the same on the "Tracker" worksheet.

Anayway, after adding "Completed" to the list of permissible entries in column "B", then try this:

Please Login or Register  to view this content.

Just another thought: it may be better, though, to put the "status" column on the far right of that data area - then inputting the data will be more intuitive. Users start from the left, fill in the data and then mark it "complete". Otherwise they have to start on the left, omit column "B", complete the data input and then go back to column "B" to finish off.

Does that do it for you?

Tim

[samjbeattie]

Works perfectly.

Also thanks for the tips, will make some amendments.

Thank you.

[harrisonland]

You're quite welcome, and thanks for the rep.

Tim

[eikenesn]

Okey i have a question,

I would like to move a row from one, to another table if a cell value goes from 1 to 0.

Have been trying to write a code but not succeeded.. appreciate any tips!