Find and Copy all Specific Information from one Sheet to another sheet

[cpassons]

Hello All-

I have searched the WWW and I can't seem to find exactly what I am looking for, so I am hoping someone here can help. I know enough about Excel to be dangerous so if you know how to help me please give me step by step details. So here is my problem:

On my first sheet I have a lot of data. I want a formula or something to look at it and find all of a specific data and copy it and all the information from the whole row onto a specific sheet. I hope the following example can clarify.

For example, if my first sheet is named "All Cases", and I have another sheet named "4". I want a formula or something to find ALL the "4" in column C of "All Cases" and copy them to my sheet named "4". Not only the information from column c but from a, b, d, e, f, g as well. And to do this automatically.

I hope this is clear as mud....

 

[My Aswer Is This]

Very few things like this can be done automatically.

Now we can write a script which every time you enter a 4 in column C that row of data will be copied to a sheet named 4.

Would this be termed as automatically by you?

Or when you activate the sheet or double click a certain cell or many other ways.
Please define automatically knowing something must happen to activate a script.

And is this all you want?
What happens if Apple is in column C or 8 is in column C

 

[cpassons]

Good question. In Column C I may enter 1, 2, 3, 4, or 5. For example Row A1 may have a Date, B1 a name, C1 the number 3, D1 other data and so on.

I would like a formula or something to find all the 3's in column C, copy it, as well at the data from A1, B1, C1, D1 to another worksheet called 3. And do this every time a 3 is entered into the main worksheet.

I'll use the same formula or whatever to find all the 1, 2, 4, and 5 and copy the information to the designated worksheet.

How it happens, either by clicking a button, or whatever, I don't care. As long as the data gets copied to the designated cell, without having to manually find and copy them each time.

If this doesn't help I'll come up with an example and try to copy on here.

 

[cpassons]

Here is an example .....

Date Name Number Color 2nd Color Relatives Location
Sept 8,2018 Smith, John 3 Red Green Mike, Mountain
Sept 8,2018 Potter, Harry 2 Blue Green Bob Beach
Sept 8,2018 Weasely, Ron 3 Purple Green Sandy Ocean

So a formula to find all the 3's in column C in this case called "Number". Then cop that whole row to an new sheet called 3. Then same for all the 1, 2, 4, and 5.
Sept 8,2018 Granger, Heromine 2 Orange Blue Stan Forest

 

[My Aswer Is This]

Well again I must ask.

If column C Row(1) value= 1 then copy this row to a sheet named 1 is that what you want?

And if column C row(2) =Apple copy this row to a sheet named Apple

And if column C row (2) equals 17 copy this row to a sheet named 17 is that what you want?

See I asked this question earlier and you did not answer it:

What happens if Apple is in column C or 8 is in column C

And have all these sheets already been created?

​

 

[My Aswer Is This]

If all the answers to my question are yes then try this:
Run this script from the sheet with all your data you want copied.

Sub Copy_Row()
'Modified 9/10/2018 11:42 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Dim Lastrowa As Long
    For i = 2 To Lastrow
        Lastrowa = Sheets(Cells(i, "C").Value).Cells(Rows.Count, "C").End(xlUp).Row + 1
        Rows(i).Copy Sheets(Cells(i, "C").Value).Rows(Lastrowa)
    Next
Application.ScreenUpdating = True
End Sub

 

[cpassons]

Sorry I missed that question. But yes, that's what I am looking to do. As you said if it contains apple, then it would copy it to the "apple"worksheet, or 8 to the "8" worksheet.

To answer your other question I have both. Sheets that were created last year, that I want to test this on, then if it works, sheets that I will use it on this year. Thanks for the quick replies.

 

[cpassons]

I am not familiar with scripts. Can you please provide more detail on what to do with this? Do I copy it exactly? Where do I put it? In a cell? Thank you

 

[My Aswer Is This]

So your saying you have never used a Vba Script.

There are numerous things you must do.
1. Create a new Workbook
2. You must save your workbook as .xlsm
You should see this Option when saving your Workbook

What Version of Excel are you using?
Are you using a PC or a Apple Computer?

Try saving your Workbook as .xlsm and see if this works for you

Then get back with me.

 

[cpassons]

I am using a Apple Computer...MacBook Air. According to the "About" menu I a running Microsoft Excel for Mac Version 16.15. I was able to save as .xlsm.

I am getting ready to go to work, so it will be later before I will be able respond again. Thank you for your continued help.

So your saying you have never used a Vba Script.

There are numerous things you must do.
1. Create a new Workbook
2. You must save your workbook as .xlsm
You should see this Option when saving your Workbook

What Version of Excel are you using?
Are you using a PC or a Apple Computer?

Try saving your Workbook as .xlsm and see if this works for you

Then get back with me.