Splitting Datasets to new TABs by column title MACRO

Hi guys many thanks for taking the time to look at my problem.

I have provided an example here to try and clarify what the problem in question is and what result should be achieved. Essentially I need a macro which can refer to column PB and go down it to find any new Schools, upon finding a new school it would creat a Tab named after that school (as shown by example tab) Upon creating the sheets the macro would proceed to copy each Matching array (All the way accross to the right of the spreadsheet the data extends as far as column "BAN", also might be worth noting this isnt continuous so their may be some blank columns amongst that) to the new tab. It would start the first array in row 16 and then each subsiquent matching array would be beneath these with one row to seperate them. My real spreadsheet is both confidential in nature, as well as far to large to upload as an example. Instead i have made the layout of this sheet the same as my 'real' sheet. One other thing to conisder is that the real sheet contains over 30,000 of these arrays. (I am aware some coding is less efficient than others.)

Thanks again for taking the time to look at this problem!

Cheers Alan

Thanks so much for the macro JBC, its very good and does nearly all that is required. There appears to be only two small errors with it (which i do not know how to fix.) Firstly in sheet1 column A there is Title1 and Title2. These are specific to each array (not always the same) so the macro needs to import them also to the new sheet. The second problem i have with it is that it has two blank rows between each array. I need the layout to be one blank row, (new row) title1, (new row) title2, then the rest of the array. I hope that makes sense? I tried to demonstrate this in the example sheet provided.

I cant thank you enough for the help, this code will be of great use to me!

Alan

Reason: corrected my initials

Lol.

Cheers for code, ill give it a whirl.

Alan

Hello JB,

The method used by this macro errors out after completion of tabs worth of data, (i.e. it splits up one tab, dies on the next) furthermore it creates problems for many of the other macros I use, as a result of creating aforementioned "connected regions." Is there anything that can be done with that macro, or does this need approaching differently?

Many thanks

Alan

Hello JB,

Apologies for the ambiguity, it fails upon production of the second tab. There is not a second tab to be processed. It is failing on the first sheet of data. This macro is one of hundreds in my current project. Is there a way of achieving the same result without the

Please Login or Register  to view this content.

This created far more problems than it solved with regards to the data analysis I am trying to undertake. I appreciate that it is difficult to understand without access to the spreadsheet.

Thanks again

Alan

Thanks guys, I simply wish to be able to code like you guys one day! Top notch!

Many thanks

Alan

In typical Alan style, I have already run into an issue with the way in which I implement this macro.

Essentially I want to run it on two different sheets in the same workbook we shall call these sheet X and sheet Y. The two different sheets have overlapping "schools", however the sheets produced are needing to be kept separately. Is it possible to import the name of the originating sheet prior to school name. I.e we could produce a sheet called "sheet X School 1" and similarly there could be a sheet produced called "sheet y School 1" when the code was run on sheet y.

I am aware this would present a further problem when we come to re run the macro on Sheet x, after an update (more data added) because now the macro would need to recognize "sheet X School 1" rather than the prior "school 1"

I hope this makes sense?

All the best

Alan

Brilliant Tiger,

Running on active sheet is fine. Many thanks. The idea behind the macro is obviously to split of a large chunk of the arrays for data analysis. One problem I have is that the amount of data I am receiving is always growing, this means that when I come to need to update these produced sheets I would currently need to clear the entire sheet. And re run the macro. However since I analyse all of this data to the right of the data, is there a way of making this macro only import new data to these produced sheets?? One way I can think of doing it would be to reference column BW lastrow in each of the sheets will contain the date of the last import.

Shall I post a new thread for this task?

Essentially it is the same splitting mechanism, but not splitting the entire sheet each time after they have been created.

All the best

Alan

Thanks again for the macro tiger, if it is a simple change is it possible to have a set cell as the reference cell and simply refer to this ? I have uploaded an updated example with the dates in. For instance we could designate a cell to be overwritten each time the macro was run. This cell would then be compared to the date in column BW? Designating a whole column seems a little inefficient considering the size of our sheet and the number of arrays.

Many thanks

Alan

Perfect (Until I find a reason why its not) Thanks as always Tiger!