Create new Worksheet for each change in Column B

It seems that a split existing data to new worksheets macro is a fairly common request, however from my searching online, I haven't managed to find one that satisfies what I need, and my VBA skills are not quite good enough to write this myself, or successfully adapt an existing version from the ones I have viewed so far, so I would be very grateful if someone had some existing code they could share that can be adapted.

The worksheet I need splitting stretches from column A to AV (perhaps further in future), and I would like the splitting to start from row 8, with row 6 being the headings and row 7 being blank. If possible, I would like the headings copied across to each new worksheet, as well as all existing formats.

Ideally the names of the new worksheets should be the same as the data in column B that the split is created on, however some of these headings have too many characters to fit into the worksheet name. Can this be managed without manual editing?

There are other existing worksheets in the workbook. These should remain.

Aside from these, I think it should be a pretty simple request for someone with a greater knowledge than me!

Many thanks in advance.

Ps. I can share a workbook if this is required but will have to create a 'non-sensitive' version for online posting. Let me know.

For the time being I have manually created a column with the formula =LEFT(C8, SEARCH("-",C8)-1) which is trimming it down to a more usable level.

I have also managed to find some code which is working to a point:

Please Login or Register  to view this content.

The issue I have now, is that my original worksheet has multiple grouped hidden columns, and the new sheets that are being created are not carrying this format over. Is there anything I can do that will fix this?

Thanks

Originally Posted by JasperD

Do your p.s.

Apologies for the delay.

I have attached the file that needs to be split via changes in column B - it has evolved and the new worksheets can be created as per column B or C. C is just a shorter version of column B to avoid the issue with the number of characters (if required).

I need the formatting to be copied across so that the grouped cells can be minimized OR a new file created with formulas changed to values and the minimised columns can be deleted for distribution.

The attachment was too big in its proper state so I have deleted the two additional tabs which have data that fulfils the lookup formualas. The macro could do with working taking into account that these will be there.

I'm very grateful for any advice or help that can be given.

The code I posted earlier is now falling down very early on, and I'm unsure how to progress.

Thanks

Originally Posted by arlu1201

If you need to hide the columns in each sheet, would it be the same columns that would be hidden / deleted for all sheets?

Hi arlu1201, many thanks for replying. It would be the same columns.

I have actually found a solution that works - i.e. some existing code, and then added some code of my own to make it work in the specific way I need. It's probably crude, but it seems to do the job. Obviously if there are glaring errors or inefficiencies then I would be glad of any advice.

Please Login or Register  to view this content.