Split Data Into Seprate Sheets By Criteria

[tigeravatar]

Hello all,

I've seen this question come up a few times, "How do I split data from a master sheet into separate sheets?"
I created a macro that is a bit generic so that users can adapt it to their needs.
Here is the macro code:

Please Login or Register  to view this content.

How to use a macro:

1. Make a copy of the workbook the macro will be run on
   • Always run new code on a workbook copy, just in case the code doesn't run smoothly
   • This is especially true of any code that deletes anything
2. In the copied workbook, press ALT+F11 to open the Visual Basic Editor
3. Insert | Module
4. Copy the provided code and paste into the module
5. Close the Visual Basic Editor
6. In Excel, press ALT+F8 to bring up the list of available macros to run
7. Double-click the desired macro (I named this one SplitDataIntoSheetsByCriteria)

[tigeravatar]

It was suggested to me that the code could be made to prompt the user to select the criteria column at run-time instead of using constants at the top of the code. Here is a version that prompts the user:

Please Login or Register  to view this content.

Credit to the suggestion goes to jeffreybrown, thanks Jeff!

[tigeravatar]

I received this Private Message from dfulmer:

Hi tigeravatar,

I have been trying to figure out a way to split data into separate sheets based on one criteria all day and then was given a link to your macro. It initially works great - everything sorted fine. However, my main sheet of data will constantly be updated and/or edited and when I re-ran the macro, it didn't move any updates that were made.

Any thoughts?

I looked over the code and realized that if the sheet name already exists, then it does not actually move the data to the existing sheet. This 3rd version addresses that issue:

Please Login or Register  to view this content.

Thank you for pointing that, dfulmer!

[robi0342]

Is it possible, to run multiple macro's at the same time. So different criteria columns go to different tabs?

Thanks!
C