Move Data Between Worksheets

[stevedes7]

I keep an on-going list of open and closed accounts. Usually the spreadsheet will look like this:

_____________________________________________________
|Account 1 | Account # | Account Name | Date Opened | Open
|Account 2 | Account # | Account Name | Date Opened | Open
|Account 3 | Account # | Account Name | Date Opened | Closed
|Account 4 | Account # | Account Name | Date Opened | Open
|Account 5 | Account # | Account Name | Date Opened | Closed


I have two other worksheets - one titled "Opened" and one titled "Closed"
How do I tell excel to have these accounts show up in the respective worksheets after I every time I write "Open" or "Close' in that last column on the first worksheet?


Basically, I want each of these accounts "CC'd" (for a lack of better terms) to either the "Opened" or "Closed" worksheets as updated.

Moreover, I want them to disappear if one is changed.


Thanks for the help

[PBisson]

Well I have an option for you.
It would involve using advanced filter. I was able to get it to work on test sheet.
Recreate two sets of identical headers to the right of the data on the same active sheet. In the column that you have open or close I want you to put open in one of the sets and closed in the other.
Two lines below again recreate the headers.

Now in the worksheet code paste something similar to this coding.
Private Sub Worksheet_Change(ByVal Target As Range)

Range("A4:B200").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"D1:E2"), CopyToRange:=Range("D4:E200"), Unique:=False
Range("A4:B200").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"g1:h2"), CopyToRange:=Range("g4:h200"), Unique:=False
End Sub

On your Open worksheet just put =if(starting cell<>"",starting cell,"") click and drag that to the appropriate size.
Same deal with Closed worksheet.

Now just hide the columns that are after your original set of data.

Sorry for how this is structured English isn't my first language.
If there is any confusion please let me know so I can elaborate.