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

C

ColletteLuffman

New Member
Joined
Jul 14, 2019
Messages
9
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
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.

​
 
Upvote 0
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
 
Upvote 0
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>​
 
Upvote 0
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



 
Upvote 0
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.

Code: 
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
VBA Question - VBA for validating value from multiple cells/column and fetching value another sheet's cell and pasting it another cell of sheet
Replies
2
Views
321
Kaper
Kaper
T
  • Question Question
VBA to populate cells on one worksheet with cells from another worksheet based on date help!!!
Replies
2
Views
385
Taekwon
T
M
  • Solved
Formula to 2-way look up a unique identifier and a date, then take the highest number from another column
Replies
3
Views
363
MrsFraser07
M
H
  • Question Question
Adjust an existing formula based on most recently added column and prior month end date.
Replies
1
Views
348
healeydm
H
D
  • Question Question
How to Copy rows from one sheet to another based on the criteria in the second sheet
Replies
9
Views
177
dsubash
D

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top