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

Date

Contract No

Type <tbody> </tbody>

Customer Name

Post Code

Site Postcode

Contract (Old or New)

Date of last Service visit to site

Start Date

Renewal Date

End Date

Reference

Payment Type

Contract Status

Band

Frequency of Service

Frequency of Service2

Preferred Service Month (1)

Preferred Service Month (2)

Preferred Service Month (3)

Preferred Service Month (4)

No. Of Pieces Of Equipment

Payment Option

Value Per Visit

Annual Service Value

Contract Type

Last Service Date

Last Service Invoice Date

Last Service Invoice amount

Next Action

<tbody>
</tbody>

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

Please provide exact specific sheet names.

Are you saying if in Column O you enter:
"New" you want this entire row copied to sheet named "New"

Or if you enter: "Old" in column O you want this row copied to sheet named "Old"

Or if you enter: "Completed" in column O you want this row copied to sheet named "Completed"​

And what is the name of the sheet where you will be entering this data. I assume this is your Master sheet.
So what is the exact name of the Master sheet.

And you want this script to automatically run when you enter a sheet named

You said:

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.

When would you ever want the row copied to more then one sheet.

​

 

[ColletteLuffman]

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

Thank you for your assistance please see my responses below

Please provide exact specific sheet names.

Are you saying if in Column O you enter:

"New" you want this entire row copied to sheet named "Active New" - Yes please

Or if you enter: "Old" in column O you want this row copied to sheet named "Active Old " - Yes please

Or if you enter: "Completed" in column O you want this row copied to sheet named "Completed" - Yes please


And what is the name of the sheet where you will be entering this data. I assume this is your Master sheet.
So what is the exact name of the Master sheet. Please call Master sheet

And you want this script to automatically run when you enter a sheet named - might be better to hit a button please




You said:
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.

When would you ever want the row copied to more then one sheet. I would also like a sheet that hall all the active whether they are old or new please

 

[My Aswer Is This]

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

In your original post you said:

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.

But in your last posting you said:
And you want this script to automatically run when you enter a sheet named

- might be better to hit a button please​

So now your saying you want the script to run when you press a button.


So now I'm getting confused.

And you said:
​

_{^{<strike>
</strike>}}When would you ever want the row copied to more then one sheet. I would also like a sheet that hall all the active whether they are old or new please

But you did not say what the name of this sheet would be.

I need all sheet names like this:

"Alpha"
"Bravo"


Put all sheet names in quotes.

And I asked for Master sheet name and you said:

So what is the exact name of the Master sheet.

Please call Master sheet​

Is the sheet really named "Please call Master sheet"

Or is it "Master sheet"

Or is it "Master"

Exact details are important.

​

_{^{<strike>
</strike>}}​

_{^{<strike>
</strike>}}​

 

[ColletteLuffman]

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

In your original post you said: Please go with this
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.


So now your saying you want the script to run when you press a button. - please run the script when you hit the button


So now I'm getting confused.

And you said:

_{^{<strike>
</strike>}}When would you ever want the row copied to more then one sheet. I would also like a sheet that hall all the active whether they are old or new please

But you did not say what the name of this sheet would be. "All Active"

I need all sheet names like this:

"Alpha"
"Bravo"


Put all sheet names in quotes.

And I asked for Master sheet name and you said:

So what is the exact name of the Master sheet.Please call Master sheet


Please use this

"Master sheet"
"Active All"
"Active New"
"Active Old"
"Completed"

Thanks

 

[My Aswer Is This]

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

Try this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the Master sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Now when you enter:

"Old"
Or
"New"

Or

"Completed"

In column O of the Master sheet the script will copy this row of data to the proper sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  7/15/2019  1:51:54 PM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = 15 Then
Dim Lastrow As Long
Dim Lastrowall As Long
Select Case Target.Value
Case "Complete"
    Lastrow = Sheets("Completed").Cells(Rows.Count, 15).End(xlUp).Row + 1
    Rows(Target.Row).Copy Sheets("Completed").Rows(Lastrow)
    Lastrowall = Sheets("Active All").Cells(Rows.Count, 15).End(xlUp).Row + 1
    Rows(Target.Row).Copy Sheets("Active All").Rows(Lastrowall)
Case "New"
    Lastrow = Sheets("Active New").Cells(Rows.Count, 15).End(xlUp).Row + 1
    Rows(Target.Row).Copy Sheets("Active New").Rows(Lastrow)
    Lastrowall = Sheets("Active All").Cells(Rows.Count, 15).End(xlUp).Row + 1
    Rows(Target.Row).Copy Sheets("Active All").Rows(Lastrowall)
Case "Old"
    Lastrow = Sheets("Active Old").Cells(Rows.Count, 15).End(xlUp).Row + 1
    Rows(Target.Row).Copy Sheets("Active Old").Rows(Lastrow)
    Lastrowall = Sheets("Active All").Cells(Rows.Count, 15).End(xlUp).Row + 1
    Rows(Target.Row).Copy Sheets("Active All").Rows(Lastrowall)
End Select
End If
End Sub

 

[ColletteLuffman]

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

Hi Not sure what i have done wrong but it doesn't work after following the steps you have said.

Please see a link to the file - i have taken out all the PII data

https://nacdo365-my.sharepoint.com/...5HvaXfYr-fe-QBwiIHyaiyyV6VVkNKqX4oQA?e=lJU48j

 

[My Aswer Is This]

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

So tell me what happened when you ran the script.
Did you get a error code?

So to run the script you needed to enter Old New or Complete in column O

 

[ColletteLuffman]

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

Nothing

 

[ColletteLuffman]

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

Scrap that it just worked