Using validation to move cells

[Guest]

Hi,
Is there any way of using a validation drop down in a cell to trigger the
movement of a row of cells from one sheet to another within a workbook?
For example:
On the "Open!" worksheet, range A1:A20 contains various values (text,
formulae, numbers etc.). A21 has a validation drop-down that selects between
"Open" and "Closed".
When I change the value of A21 from "Open" to "Closed" I want to transfer
the contents of A1:A21 to the worksheet "Closed!"
TIA
Dave

 

[Guest]

Hit Alt F11
Select the Sheet where the data is from the project explore window by
clicking on it, then on the dropdow box in the code window select worksheet
you should get the following:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

In Between the two lines type this:
If Range("A21") = "Closed" Then
Range("A1:A21").Copy Destination:=Worksheets("Closed").Range("A1")
End If

--

If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

 

[Guest]

Michael,

Thanks for the response.

Is there any way of modifying this so that if In have, say 100, lines each
can be moved indivuidually as the status is changed to "Closed" without
overwriting previous entries (I'm thinking some sort of do... while... loop)?

TIA

Dave

 

[Guest]

First set the number of lines, if you base it on the last cell of the column
where the data is you could use something like this:

iLastRow=Range("A65536").end(xlUp).row
This should tell you how many rows to check on your loop. Now the loop:

For i =1 to iLastRow
If Range("A" & iLastRow) ="Closed" then
Range("A" & iLastRow).Copy Destination:=Worksheets("Closed").Range("A" &
iLastRow)
End If

This will run in one step, meaning, you will have to do the data validation
for all of the cells before you run this code. The code should be attached to
a button and executed after the data validation has occurred. It will place
the data in the same exact spot except it will do it in the "Closed" sheet.

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.