VBA to copy certain data from sheet into another sheet - URGENT HELP PLEASE

[Alice21]

Hi

I have been Googling for ages to try find an answer to this but to no avail

I have an Excel sheet with over 8000 entries/rows.
Column A contains ID numbers (there are approx 89 different ID numbers).
I wish to group all the same IDs and copy them into a new sheet therefore leaving with me 89 sheets, each containing data for just that ID.

Can this be done please without me having to manually filter for the ID's and then manually copying and pasting the relevent records into new sheets??

I also have a very tight deadline to create this report hense trying to find a VBA to do the work for me!

Many Thanks!!

[Pepe Le Mokko]

For urgent matters, paying sites will be willing to do this quickly.

[abduljaleel.mca]

Send me the excel file which contains 8000 rows.

[Alice21]

It contains mainly confidential patient information, all i could send you would be column A.

[abduljaleel.mca]

Not a problem, send it with column A only.

[Alice21]

Here you go...

[james.halliwell]

a pivot table will do this for you

1. pivot your data,
2. putting your ID into the report filter section of your pivot table field list
3. from piviot table tools click on the options tab from the ribon
4. click on the dropdown from options (underneath pivot table name)
5. select // show report filter pages
6. double click id numbers

that will then create a tab for each of your id numbers will all the data in it

hope it helps

[Alice21]

I have done this previously but it's just time consuming clicking on over 89 ID numbers to create the seperate sheets. I have 4 Excel reports to do, so it would mean id have to repeat this process 4 times..and it has to be done monthly. Hence trying to find an easier automated way of doing it

[abduljaleel.mca]

Use the attached macro.

[james.halliwell]

I have done this previously but it's just time consuming clicking on over 89 ID numbers to create the seperate sheets. I have 4 Excel reports to do, so it would mean id have to repeat this process 4 times..and it has to be done monthly. Hence trying to find an easier automated way of doing it

sorry i think you miss understand me, the pivot will create a tab for each ID see attached for an example

[Alice21]

Hi - Thank you for your help...

abduljaleel.mca - That macro only put one record on each sheet. I need all records related to each ID number copied into each sheet.

James - The try me macro would not work, it came up with a debug error

[abduljaleel.mca]

U mean ID and its related data to be placed in a sheet...right?

[Alice21]

Yes, there may be many records for each ID number. I need all the rows copied into the sheets.
Thanks

[abduljaleel.mca]

k fine, wait for some more minutes.

[james.halliwell]

what the below video

http://www.youtube.com/watch?v=5yj1a2VWpQ4

cheers

[Alice21]

Thanks. I can't view youtube in work, but i'll have a look later at home.

[abduljaleel.mca]

Use the updated macro

[Alice21]

Hi - that macro just did the same thing as the first.
This is how I would like it to look...

[AB33]

Try the attached.
Initially had created all the sheets, but need to delete few sheets as the data had exceeds this forum's limit. There is a button on first sheet. When you run it, it will create all the sheets again.

[abduljaleel.mca]

Oops...That's why I asked for the sample output earlier itself.
I am working on it, will get back to you.

[Alice21]

Thank you AB33 - that worked perfectly!
Is there a code that could be added to ensure the columns adjust correctly in the new sheets?

[visha_1984]

try this one

Please Login or Register  to view this content.

[AB33]

Alice,
You mean if added more columns, the code will reflect these according? Yes. The code clears all existing data and repopulate them again with fresh data

[Alice21]

I would like all columns in the new sheets to auto adjust to show the cell contents, I don't want to have to manually adjust the column widths for all the sheets.
I'd also like the headings in the master tab to copy across into each sheet.
Am i asking too much now?

[AB33]

Try the attached

[Alice21]

That worked like a charm!! Thank you ever so much!
I'm slowly getting my head around VBAs..just wish i knew more!

[AB33]

Alice,
You are welcome! and thread closed