copying selected data from sheet to new sheet

I am looking for a macro to select a set of data and paste to a new sheet , I have around 6000 lines of data defined by an ID number which has been produced by another software package, but I need to copy each individual (ID number) to a new sheet. Each ID has varies in the number of lines of data.

Hi Neil. For clarification ... do you want to have all the rows with the same ID number on a separate sheet? Perhaps you could post a copy of your file to make it easier to understand.

Yes, exactly.

I have plodded through by copying and pasting the code selecting 22,23,24 rows depending on how many rows corresponding to each ID, but not sure if there is an easier/faster way to code the macro to select rows with the same ID?

File attached

Where is the source sheet that contains all the data?

Its the bcarcmh tab

Hi Neil. Please make a backup of your file and then, working with the backup, make a few changes. Copy the code below into a regular module. Then delete all the sheets except for your data source sheet. After running the macro, sheets will be automatically created and named according to the ID number and then the data for each ID will be copied over. Please be patient after you click the button. Since you have up to 6000 rows, it can take a little time to complete the task. Let me know if this suits your purpose.

Please Login or Register  to view this content.

Thanks for all you help, works beautifully.

Is there a textbook that you could recommend for programming in visual basic to help me work through some of these problems myself?

Hi Neil. I'm glad that everything worked out for you. I would imagine that there are many, many books available but I'm not familiar enough with any of them to make a recommendation. I find that by doing searches on the web, I can usually get the information I want. There are also on-line videos and tutorials that may be useful. Forums, however, have been my main source of help. I find that the more code writing I do, the more I learn and since I'm retired, I have the time to make it a hobby. Good luck!