Excel 2007 Macro to break up data into multiple sheets based on criteria


I have data on an Excel 2007 spreadsheet that needs to be broken up into multiple spreadsheets, and further broken up on each spreadsheet based on criteria --  with a tiny bit of formatting to boot. If someone could help out, I would greatly appreciate it!

I have attached a sample file with the source and the desired result, but a bit of explanation and attention-drawing is required (all of this is demonstrated in the sample file, but you might miss it just by looking):

-- The data should be divided into separate sheets based on Category 1.
-- On each generated sheet, the data should be broken down into sections by Category 2, with the formatting the same as in the sample file (Category name in bold size 14, line skipped, headers in bold,data below, line skipped, category name in bold size 14, etc..)
-- The Fruit sections, which have no data in the Data 1 column, should not have that column on the result sheets.

-- IMPORTANT NOTE: Category 1 (which determines the sheets) has a category called 'All'. Data for the 'All' category should NOT get its own sheet, but should appear on EACH generated sheet, broken up by Category 2 appropriately.

Very grateful for any help!

Please let me know if the explanation or the sample file are unclear.


Who is Participating?
TracyConnect With a Mentor VBA DeveloperCommented:
OK, these changes should address the problems in your last comment.

TracyVBA DeveloperCommented:
Have a look at this. This should get you started.  It creates all the sheets and adds all the data to the sheets accordingly and then sorts it by Category 2.

I haven't gotten to the formatting yet, or removing the blank row.  The rest of it's in there though.  That's all I have time for right now, I can circle back later and see if there's any other posts.

andreyman3d2kAuthor Commented:
Thanks, but unfortunately my VBA knowledge is not up to task to build on this.
It breaks the data up into sheets, but it:

-leaves the the Category columns in place
-leaves out the headers
-does not create category 2 as headers
-leaves the Data1 column for data that does not have anything in Data1

I can perform basic code modifications, but this beyond my cranium : )

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

TracyVBA DeveloperCommented:
OK, I've added in the formatting.

Try this.

andreyman3d2kAuthor Commented:
Broomee, thanks, this is very close -- 3 things:

1. Would it be possible to not include the category 2 column in the final output, since it already becomes the header section?

2. For some reason, after running the macro, the NJ tab is fine, but on NY and CT I get a '3T' in the fruit header... like this:

Category 2      Data 2      Data 3      3T

3. Not all the data is carrying over -- CT lost the pumpkin record. Also, the first Fruit is vanishing --- (I think that's there the extra 3T is coming from)
andreyman3d2kAuthor Commented:
Hey, this is great, thanks very much!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.