Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using validation to move cells
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using validation to move cells
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. "Risky Dave" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using validation to move cells
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 "Michael" wrote: 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. "Risky Dave" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using validation to move cells
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. "Risky Dave" wrote: 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 "Michael" wrote: 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. "Risky Dave" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically move entire row by cell validation | Excel Discussion (Misc queries) | |||
Sorting cells: a list behind the cells do not move with the cell | Excel Discussion (Misc queries) | |||
Move Active Cell after Data Validation List | Excel Programming | |||
Data Validation Cell - Move to UserForm | Excel Worksheet Functions | |||
Two validation cells populated third validation | Excel Programming |