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]

If you look at your Menu Bar do you see a icon for Visual Basic.
If so click on the icon
Now find the icon that says Insert Module and click on it.
Then Paste this code into the module:

Now you must create a shape on the workbook

Right click the shape and choose Assign Macro
And you should see a Macro named MyName

Choose this macro

Now any time you click on the shape your macro will run.

This Test Macro will only give you a Message box popup saying "Hello George"

Now if all this works

Then do the same thing but put in the shape the script I previously provided.

Be sure the shape in on the same sheet with all your data you want copied.


This is a Test script:

Sub My_Name()
MsgBox "Hello George"
End Sub

 

[cpassons]

Hello Again-

Thank you again for your help. I ran the test script just as you said on a blank Excel file. So I tried to run the original script following the same method, but on an old file and it gave me an error.

Run-time error 9
Subscript out of range.

When I click on debug it took me to:

Lastrowa = Sheets(Cells(i, "C").Value).Cells(Rows.Count, "C").End(xlUp).Row + 1

Please help......or continue to help. Thank you

 

[My Aswer Is This]

OK Great you now know how to run a script since the test script worked.

Now on this second script the script looks in Column C for a sheet name

So if in column C you have George the script looks for a sheet named George
If you have Bob in Column C the script looks for a sheet named Bob

The names must be exact. You cannot have George Smith in column C unless you have a sheet named George Smith

Are you sure you have all the values in Column C exactly as the sheet name? Spaces and spelling must be exact.

 

[Fluff]

Another question
Are your sheet names actually numbers like 4, 5, 6 etc?

 

[My Aswer Is This]

Another question
Are your sheet names actually numbers like 4, 5, 6 etc?

And why would that matter?

 

[Fluff]

If for a given row Cell(i,"C").Value was 4, then

Sheets(Cells(i, "C").Value)

would be

Sheets(4)

ie. It would be using the sheet index, rather than the sheet name.
This is easily fixed like

Sheets(CstrCells(i, "C").Text)

 

[cpassons]

Thank you both for your contribution to my problem. My sheet names are 1st, 2nd, 3rd, 4th and 5th. In column C of the first sheet labeled ALL CASES, the names(numbers) are the same.

So if I have 500 rows of data on the sheet called "ALL CASES" I want a script to look at column C find all the "1st" and copy the entire row to the sheet labeled "1st" and so on.

Thanks again.

 

[My Aswer Is This]

Well if you run my script provided from the sheet named

ALL CASES

The script should do what you asked.
I suspect some of the values in column C are not sheet names.

I suggest you try do this with about 5 rows of data in sheet named

ALL CASES

And see what it does.

Like I said earlier the value in column C must be exactly as the sheet name.

Show us a example of what your sheet named

ALL CASES looks like.​

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

<strike></strike>
​

 

[cpassons]

Hello again-

I verified that all data in Column C is exact as the sheet names and still get the error. I can't copy an exact example. But here is the gist.

Column A- Date
Column B- Name
Column C- Number
Column D- Issue
Column E- Actions
Column F- Comments

So basically I want to sort/them and have them go to their sheet based upon the number they have in column c.

Does this help at all?

The Error says it's somewhere here...

Rows(i).Copy Sheets(Cells(i, "C").Value).Rows(Lastrowa)

 

[Fluff]

Add this line as shown

    For i = 2 To Lastrow
     [COLOR=#0000ff] Debug.Print "|" & Cells(i, "C").Value & "|", Evaluate("isref('" & Cells(i, "C").Value & "'!A1)")[/COLOR]
        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

Then in the vbe look to see if the Immediate window is open (normally below the code window) Ctrl G will open it if needed. When it's open run the code & when it fails copy & Paste the last line in the immediate window to the thread