I have created a worksheet to reflect project status. One of the columns is, surprisingly, "Project Status", which calls from a list of values. One of these values is "Completed".
I have two relevant sheets in the workbook, both with identical column structure: one to reflect projects In Flight, and the second to show Completed projects, so my objective is to implement code which would validate the project status cell (column R), and where the value is "Completed" the entire row will be copied to the Completed sheet and then deleted from the In Flight sheet.
I would also need a reverse solution to allow for human error. So in case the status was updated in error and the row moved, the user could then access the "Completed" tab, change the status back to something else other than "Completed" and then the row would be moved back to the In Flight sheet.
The row to be occupied, in either direction would be the next available empty row.
Hope that makes sense to someone...!
I have tried this, and it appears to do more-or-less what I am trying to achieve, but then Excel freezes! I think it is going into a loop somewhere but cannot locate it.
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Set i = Sheets("InFlight") Set e = Sheets("Completed") Dim d Dim j d = 4 j = 5 Do Until IsEmpty(i.Range("R" & j)) If i.Range("R" & j) = "Completed" Then d = d + 1 e.Rows(d).Value = i.Rows(j).Value i.Rows(j).EntireRow.Delete End If j = j + 1 LoopEnd Sub
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
-Mike Kapnisakis, Warner Bros
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.
Our community of experts have been thoroughly vetted for their expertise and industry experience.