Hello, I'm a VBA-noob and don't know much about coding in general so I figured I should ask someone who does. I hope someone with more knowledge than me, could help me sort this mess out
I have a simple projectlist on sheet1 formatted like this:
Column A=Projectname, B=Description, C=Owner, D=Deadline and E=Status.
On sheet2 I have the same columnheaders.
What I would like to do, is that if Status changes to "Done",
* the entire row should be copied to a free row on sheet2, or just inserted on sheet2.row2 without overwriting anything
* the now empty row on sheet1 should be deleted (so there're no empty rows)
* the data on sheet2, should be sorted with D as first key and C as second.
Then, if Status on sheet2 changes to "Reopened", the same procedure should happen but from sheet2 to sheet1.
My first choice was to create a checkbox on each row in the Status column,but I noticed it didn't seem to follow with cut/paste even though I changed the property Locked to false. Anyone knows if it's possible to change this behaviour?
I only know small bits of VBA but my idea is this:
Write a sub that searches D2:D200
for "done" or "Done", selects the cell that contains it and use something like Target.EntireRow.Cut Sheets("sheet2")."<sheet2.first empty row>"
and then Target.EntireRow.Delete...
I don't know enough VBA-coding, but can "<sheet2.first empty row>" be like a global variable derived from another sub and always present, or do I need to call that sub from the search and move sub? If so, should I do it before it searches Status for a match?
Is there a way to insert the row on ie A2, pushing the existing rows on sheet2 down a notch? That would be the best way.
Should I put number of the Status matching row on sheet1 in a variable, so that I can use that row number to delete the row, or will it work to both cut/paste and delete it based on Target? I'm afraid that the row loses it's Target status once I paste it into another sheet? When I've tried this, it seems to sometimes delete the row above instead of the correct one.
I have a working sort sub that looks like this:
Should I have a separate sub for this and call it from the other, or should I just paste this code into the big sub?
.Range("A:D").Sort Key1:=Range("C2"), Order1:=xlDescending, Key2:=Range("B2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _