Using validation to move cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Risky Dave - 25 Sep 2007 13:12 GMT

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

Reply to this Message

Michael - 25 Sep 2007 15:32 GMT

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

Signature

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

Michael Arch.

> Hi,
> Is there any way of using a validation drop down in a cell to trigger the
[quoted text clipped - 7 lines]
> TIA
> Dave

Reply to this Message

Risky Dave - 25 Sep 2007 15:56 GMT

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

> Hit Alt F11
> Select the Sheet where the data is from the project explore window by
[quoted text clipped - 19 lines]
> > TIA
> > Dave

Reply to this Message

Michael - 25 Sep 2007 16:16 GMT

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.

Signature

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

Michael Arch.

> Michael,
>
[quoted text clipped - 31 lines]
> > > TIA
> > > Dave

Reply to this Message