Automatically take rows from one sheet to various anothers based on 2 criteria

[ColletteLuffman]

HI
This is my first post so please be gentle. I am not great with VB so might need some hand holding

I have a spreadsheet will lots of columns (about 30) and 2000 rows


I would like to use this spreadsheet to populate multiple other sheets but keep the data in the master sheet.

Each time we add a new row to the master the row of data needs to be moved to 1 or multiple sheets depending on criteria.

List of fields below

[TABLE="width: 5181"]
<tbody>[TR]
[TD="align: right"]Date[/TD]
[TD][/TD]
[TD]Contract No [/TD]
[TD="align: left"]

Type

<tbody>
</tbody>

[/TD]
[TD]Customer Name[/TD]
[TD]Post Code[/TD]
[TD]Site Postcode[/TD]
[TD]Contract (Old or New)[/TD]
[TD]Date of last Service visit to site[/TD]
[TD]Start Date[/TD]
[TD]Renewal Date[/TD]
[TD]End Date[/TD]
[TD]Reference[/TD]
[TD]Payment Type[/TD]
[TD] Contract Status[/TD]
[TD]Band[/TD]
[TD="align: right"]Frequency of Service[/TD]
[TD="align: right"]Frequency of Service2[/TD]
[TD]Preferred Service Month (1)[/TD]
[TD]Preferred Service Month (2)[/TD]
[TD]Preferred Service Month (3)[/TD]
[TD]Preferred Service Month (4)[/TD]
[TD]No. Of Pieces Of Equipment[/TD]
[TD="align: right"]Payment Option[/TD]
[TD="align: right"]Value Per Visit[/TD]
[TD="align: right"]Annual Service Value[/TD]
[TD]Contract Type [/TD]
[TD]Last Service Date[/TD]
[TD]Last Service Invoice Date[/TD]
[TD="align: right"]Last Service Invoice amount[/TD]
[TD]Next Action[/TD]
[/TR]
</tbody>[/TABLE]

eg The new row is added.

If column O is Active and Column H is New then put in Active New Sheet
else If column O is Active and Column H is Old then put in Active Old Sheet
else If column O is Complete then put in Complete Sheet

Hope this makes sense

Thanks
Collette

 

[My Aswer Is This]

Re: Automatically take rows from one sheet to various anothers based on 2 criterial

So assuming it now works.
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[ColletteLuffman]

Re: Automatically take rows from one sheet to various anothers based on 2 criterial

Sorry one last question

If changes are made to the row in time will the row be added again or over write the old one please?
Thanks
Collette

 

[My Aswer Is This]

Re: Automatically take rows from one sheet to various anothers based on 2 criterial

It will be added as a new row.

You must have not used the script yet or you would have seen how it works.
Have you not used it yet?
Is this not what you want?

 

[ColletteLuffman]

Re: Automatically take rows from one sheet to various anothers based on 2 criterial

Sorry not sure i made myself clear

The master sheet is always being updated. if someone were to make a change to the row in master sheet eg old to new - would it add another row to Active All or overwrite the previous written row ?

Thanks

 

[My Aswer Is This]

Re: Automatically take rows from one sheet to various anothers based on 2 criterial

It would add another row.
My question is. Why do you not run the script by entering one of those values like Old New or Complete into column O of the Master sheet and you will see how it works.
I asked have you tried this and you did not answer my question.

Sorry not sure i made myself clear

The master sheet is always being updated. if someone were to make a change to the row in master sheet eg old to new - would it add another row to Active All or overwrite the previous written row ?

Thanks

 

[ColletteLuffman]

Hi it all works just wondered if there was a way to check if the row was already in the other sheets and if it was over write with the new info rather than writing another line.

 

[My Aswer Is This]

Well that could be done if there is something on that row which is unique that could identify that row.
And this could get more complicated. If user enters Old into column O row is copied to both all sheet and Old sheet

If user then enters New in column O row is copied to New sheet and all sheet
And on and on.

I'm not understanding why user would enter old in column O and then later enter old again on same row and then maybe later enter new