Splitting Excel sheet into multiple sheets based on random values of a column.

[Preeti1309]

Hello Everyone.



I am completely new to Excel Macros. My excel sheet contains random values in column B. I need to split the excel sheet based on highlighted cells in column B. Each tab should have the all the data below that highlighted cell of column B, till the encounter of next highlighted cell and also the tabs should be renamed according to that highlighted cell. Also the original formatting should be retained in each separate sheet. Column B cell is also merged with column C, D, E some times.

Data looks like this...

Proposal Total
Some data

Task: 1
Some Data

Task:2
Some Data

Task:3
Some Data

Task:4
Some Data

Here, I need 5 tabs... Proposal Total, Task:1 Task:2, Task: 3, Task 4 with the data below them in their separate sheets. Task: # here is dynamic and can vary from report to report. I am also attaching the actual excel sheet, I have been trying hard to get this but no luck.

Thanks in advance,
Preeti

[sktneer]

Try this code to see if this works as per your requirement.

Please Login or Register  to view this content.

[mikerickson]

I think this will do what you want. Some of the highlighted cells have illegal sheet names.

Please Login or Register  to view this content.

[Preeti1309]

Hi Stneer,

Your code works, the only issue is Task Number in my report is dynamic and it may vary from report to report. Their can be a scenario where my report returns only two Task Numbers e.g. Task: 1, Task: 3 and that too not in series. Also I need to retain the original formatting.

Regards,
Preeti

[Preeti1309]

Hi Mikerickson,

My sorry for confusion but I have highlighted the cell just to create understanding of what needs to be done..The original report will not have any colored background for those cells in it.

Regards,
Preeti

[sktneer]

Hi Stneer,

Your code works, the only issue is Task Number in my report is dynamic and it may vary from report to report. Their can be a scenario where my report returns only two Task Numbers e.g. Task: 1, Task: 3 and that too not in series. Also I need to retain the original formatting.

Regards,
Preeti

Did you run the code? Delete some of the highlighted cells (like Task : 1 and Task : 3) to see if the code creates the sheets Task2 and Task4 with the desired data.
Moreover the sheets will retain the original formatting too.

**Delete all the newly created sheets before running the code again.

[Preeti1309]

Hi Sktneer,


Formatting is getting distorted for me.. It is fine after point 11 but before that cells are getting expanded hugely.

Regards,
Preeti

[sktneer]

Try this code to see if this helps.........

Please Login or Register  to view this content.

[Preeti1309]

Hi Sktneer,


Thanks a lot for this... You have been a Savior for me today.. Keep up the good work..Once again thankuu...


Regards,
Preeti

[Preeti1309]

Hi Sktneer,


The current code is not generating tab for task:4.

Regards,
Preeti

[sktneer]

Are you running the code on the workbook which you uploaded with your post#1 or you are trying this code on a different workbook? This kind of code completely depends on the arrangement of the data in the sheet. So if you are running this code on a workbook in which the data arrangement is different from the one which you attached here, you may face issues with it.

It would be better if you attach the workbook which is not responding to the code to let me know the issue with the code.

[Preeti1309]

Hi Sktneer,

I am running the code on the original sheet that I have provided in my post. Although the task Number in my case is dynamic they may increase. till task - 7.

Regards,
Preeti

[sktneer]

Please try this code to see if it is working as per your requirement....
Moreover it hardly matters that how many tasks are there in your sheet, the only thing matters is the data arrangement. Each set of data for all the tasks must have the same data arrangement. Like no. of blank rows between each task should remain constant etc.

Please Login or Register  to view this content.

[Preeti1309]

Hi Sktneer,

The code works fine now..Thanks a lot!!! Is there any way we can remove the borders from all the child tabs..Like the one in the main tab.

Regards,
Preeti

[sktneer]

Try this......

Please Login or Register  to view this content.

[sktneer]

If you get formatting issue again, Change the code in red in the above post#15 to the following code........

Please Login or Register  to view this content.

[sktneer]

Oh sorry, you were talking about the child tabs.....In this case try the code given below........

Please Login or Register  to view this content.

[Preeti1309]

This code changes nothing in the result. Can we change the border color as white?

Regards,
Preeti

[sktneer]

Did you try the code given in post#17? The code was supposed to clear all the borders from the newly created sheets. Do you still find borders in newly created sheets?

[Preeti1309]

Yes.. It didn't work for me.. attaching the sheet that I am getting...

[sktneer]

Tell me where do the borders exist in child tabs. Let me know the sheet name and the cell address. To confirm that the newly created sheets have borders in them, see the print preview of newly created sheets, borders are confirmed if you see them here in print preview.

[Preeti1309]

I don't see the borders in print preview mode..In the noraml mode they are present..It is a light gray border and present in all the child tabs...But I think I cen provide the demo in Prnit Preview mode.. Thanks a lot for helping me out here...

Keep up the good work!!!

Regards,
Preeti

[sktneer]

No these are not borders, they look like borders because of merged cells and low column width. If you have still a doubt, take the printout of a sheet and if there are any borders they will be reflected on the printout also.

[Preeti1309]

Hi Sktneer,

I am running your code in the attached report with the same data. For task - 4..it is not returning any data.


Regards,
Preeti

[sktneer]

I told you that the code will run successfully on any workbook where the data layout is exactly same as one you uploaded in your first post. Anyways try this code.....

Please Login or Register  to view this content.

If that works, you may also click on * (star) to Add Reputation which is another way to say thanks to those who have put their time and efforts to help you in this forum.

[Preeti1309]

It works.. Thanks a lot again..

[sktneer]

You have to be careful with the following lines of codes to see if they match your worksheet.
The first is

Please Login or Register  to view this content.

Here 3 is the first column (col. C) where data exist for each task. In your earlier sheets it was 2 (col. B). So make sure you are referencing the correct column in the code.

and the second is

Please Login or Register  to view this content.

Here the code assumes that your Task are listed in col. B which is ok here. But make sure that tasks are listed in col. B and if not make the proper change in the code.

Hope that helps.

[Preeti1309]

Hi Sktneer,

This helps... Thanks a lot Stuck in one more issue.

http://www.excelforum.com/excel-prog...ml#post3670961

Regards,
Preeti