How to move a row when a particular cells status changes

January 5, 2015 at 08:21:30
Specs: Windows 7
I am needing to create a macro that will perform the following actions:

Ex. I have a sheet that has the following values selected from a data validation list of "-","Open" and "Closed". Once I select "Closed" in that column (column "G") I need the macro to run and move that rows cells from columns "A" through "H" to Sheet2's next available row and then move the existing data rows in Sheet1 up (essentially the same as if you manually deleted that row) to fill in the row that was moved.

Initial Sheet1
"A" "B" "C" "D" "E" "F" "G" "H"
1 Jan Smith Pr1 Co1 $x.xx Date1 Open Date2
2 Jan Doe Pr2 Co7 $x.xx Date1 Open Date2


Initial Sheet2
"A" "B" "C" "D" "E" "F" "G" "H"
1
2


Changed Sheet1
"A" "B" "C" "D" "E" "F" "G" "H"
1 Jan Smith Pr1 Co1 $x.xx Date1 Closed Date2
2 Jan Doe Pr2 Co7 $x.xx Date1 Open Date2

^^^Row1 above should be moved to Sheet2 via the macro since cell "G1" was updated to "Closed".


Updated Sheet2
"A" "B" "C" "D" "E" "F" "G" "H"
1 Jan Smith Pr1 Co1 $x.xx Date1 Closed Date2
2

^^^Row 1 was filled in with the information from the Changed Sheet1. Now whenever ANY rows "G" cell is updated to "Closed" Sheet2 needs to have the next available row filled with that information.

message edited by K.Haub


See More: How to move a row when a particular cells status changes

Report •


#1
January 5, 2015 at 15:46:13
Please click on the blue line at the end of this post and read the instructions on how to format example data so that it is easier for us to read. Then edit/repost your data so that the columns line up correctly. Don't forget to use Column letters and Row numbers as shown in the example.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question