Moving data from one sheet to a new sheet based on value

**learning_vba** · 04-27-2011, 12:27 PM

Hello,
I have been searching the forum for a solution to this problem, but I cannot seem to find what I am looking for. I have a worksheet with 4000+ rows of data. In Column B of the worksheet are the codes I need to sort by, count the number of unique values, and then take each group of unique values and copy them into a new unique worksheet. I cannot figure out how to do this.
Obviously starting the macro with sorting smallest to largest in Column B is necessary, and then I was thinking of counting total number of rows, unique values in all of the rows, then somehow count the number of rows associated with each unique value in Column B, then using some offset action and counter variables to do the copying and pasting to new sheets. However, I am very stuck and cannot seem to make any progress. If someone could help me out, I would certainly appreciate it.

Thank you very much for the help. I have included a small sample worksheet that shows what the raw data looks like (sheet DATA) and the following sheets are the desired end result.
Thanks again.

**tigeravatar** · 04-27-2011, 01:05 PM

learning_vba,

The sort isn't strictly necessary, but I included it anyway. The following macro should accomplish what you're looking for:

Please Login or Register  to view this content.

Notes:
-Make sure the wsData is set to the correct sheet (the sheet that contains all of the data)
-Make sure the rngKey is set to the correct cell (the first cell in the data on which it should be sorted, B2 in your sample workbook)

Hope that helps,
~tigeravatar

**learning_vba** · 04-27-2011, 01:24 PM

tigeravatar-

Thank you so much- this works perfect. I really appreciate it!!!

**learning_vba** · 04-27-2011, 02:17 PM

The code will not work on the attached workbook. Thanks for looking at it again.

**tigeravatar** · 04-27-2011, 02:24 PM

learning_vba,

I put in the macro in the test1 workbook and it worked fine for me. I have attached a modified copy of that workbook that includes the macro. When I tested it, I did not get any errors and it performed as expected. Let me know if the modified copy is giving you problems, too.

~tigeravatar

**learning_vba** · 04-27-2011, 02:27 PM

Originally Posted by tigeravatar

learning_vba,

I put in the macro in the test1 workbook and it worked fine for me. I have attached a modified copy of that workbook that includes the macro. When I tested it, I did not get any errors and it performed as expected. Let me know if the modified copy is giving you problems, too.

~tigeravatar

Yes- it works now. For some reason, some of the newly created sheets have all of the cells in white font so you cant see them, but that is something I can fix. Thank you very much for all of your help today!