Code to create a list of records in another sheet, based on the entryof the word "Terminated" in a g

[Mike C]

Hello - Any chance someone has code that would allow the following.

In sheet1 I have a list of names that are being added as active
members. When the members leave or terminate, we enter the word
"terminated" in a column titled "Status."

What I need is a record of all names that were terminated (on a
separate sheet). So, ideally, I can come up with code that will
automatically feed (a copy of) that entire record to a separate tab in
that workbook, upon the changing of the status in that column to
"terminated". The name of that other sheet is "Terminated Members."

I will need that list of terminated members to stay in the "terminated
members sheet", although I will eventually delete the entire record
from from the Active Members sheet.

Any code that would assist (or that is used for a similar set of
circumstances) would be greatly appreciated.

Thanks.

 

[Norman Jones]

Hi Mike,

I would suggest a non-VBA approach:

- Enter (and retain) a table of all members.

- As now, enter 'Terminated' in a status column
(although I sincerely hope that the description
is not intended literally!)

- Set up an Advanced Filter for the table (database)
For a tutorial and multifarious information on the
Advanced Filter, see Debra Dalgleish at:

Advanced Filters -- Introduction
http://www.contextures.com/xladvfilter01.html

and

Advanced Filter -- Complex Criteria
http://www.contextures.com/xladvfilter02.html

The filter will enable you to see the membership
data in a myriad of ways in seconds. It will also
enable you to extract a sub-members list to another
sheet; the sub members list may be based on any
criteria. e.g. Terminated, living, under 30 etc.

Should you require to automate any of these steps,
post back with details.