Forum Discussion
VBA: Moving a row to new sheet based on list item
Hello,
I have a workbook with two sheets: 'Live or Hold' and 'Archive'.
The 'live or hold' sheet has a column with a data validation list, containing the list items: 'Open', 'Hold', 'Archive'
When 'Archive' is selected in the drop-down list, I would like it to automatically move (or cut and paste) the whole row to the 'Archive' tab, so that we have a record of all the archived data. Preferably, this should happen when 'Archive' is selected from the list; I'd rather not assign the macro to a button as this workbook will be used by multiple people.
I'm not an advanced VBA user but I can do some basic stuff.
Any help appreciated
Regards
Chris
VBA is not needed. You may use the FILTER function. Please see attached sample workbook.
Hi Patrick2788,
Thanks very much for your response.
Is there a way to automatically delete the row from 'Open or Hold' when archive is selected from the list and it moves over to the 'archive' sheet?
So its almost like cutting and pasting the row over to the archive tab when archive is selected from the drop-down and deleting the blank row which is left.
In any case, I'm one step closer to the goal so thank you!
Chris
- Automatically deleting the row will require code.
There are ways to conditionally format the rows to be removed so they appear 'blank' but that's a cell formatting trick - window dressing (The actual rows wouldn't be deleted).
