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]

Yes a blank cell would cause a error. You do not have a sheet named nothing.
I suggest you run the script on a sheet with only about 4 rows of data and see if it works.

 

[My Aswer Is This]

Show us a example of your sheet names.
Here are 3 examples.
Are they like 4 5 10

Or like Apple Pie Orange

 

[vcoolio]

Hello Cpassons,

I've just read through this thread and I thought that I'd add my two cents worth.

Fluff's post #20 determined that you have an incorrect value (3 and 2) in Column C so you'll need to correct this to only have one or the other sheet name in the relevant cell.

Based on what you have told M.A.I.T., you've also found some blank cells in Column C.

Hence, I've added below a code that may just sort out your issue:

Option Explicit
Sub TransferData()

        Dim ar As Variant, ws As Worksheet, sh As Worksheet
        Dim i As Integer
        
Set sh = Sheets("All Cases")

Application.ScreenUpdating = False

sh.Range("A2", sh.Range("G" & sh.Rows.Count).End(xlUp)).Sort sh.[C2], 1
ar = sh.Range("C2", sh.Range("C" & sh.Rows.Count).End(xlUp))

For i = 1 To UBound(ar)
         Set ws = Sheets(CStr(ar(i, 1)))
         ws.UsedRange.ClearContents
         sh.Range("C1", sh.Range("C" & sh.Rows.Count).End(xlUp)).AutoFilter 1, ar(i, 1)
         sh.Range("A1", sh.Range("G" & sh.Rows.Count).End(xlUp)).Copy ws.[A1]
         ws.Columns.AutoFit
         sh.[C1].AutoFilter
    Next i

Application.CutCopyMode = False
Application.ScreenUpdating = True

MsgBox "Data transfer completed!", vbExclamation, "Status"

End Sub

 
'**  CStr function converts numbers to text. This needs to be done when  numbers are used as tab names otherwise the code won't work.

If you are using just numbers as sheet names, then the above should work just fine for you.

The code will convert the sheet names to text. It will also sort Column C to place any rows with blank values in Column C at the bottom of the data set and thus not cause you grief. I've assumed that you are just dealing with data from Columns A - G.

Following is the link to a small sample to show you how it works:-

http://ge.tt/9YAxOdr2

You'll note that I've left a couple of blank cells in Column C. Click on the "RUN" button to see it work. The code also "refreshes" each individual destination sheet every time that it is run.

If you wish to test it in your own workbook then please create a copy of it first and test it in that. Remember to correct the 3 and 2 problem.

Just another option for you.

Cheerio,
vcoolio.