Breaking a Table in to New Sheets

Hey folks,

I was just wondering if there was an easy way to split several rows of a sheet in to their own worksheet? Right now I have a workbook with one worksheet which looks like:

R1 (C1: C8): Data
R2 (C1: C8): Data
R3: All Empty
R4 (C1: C8): Data
R5: All Empty
R6 (C1: C8): Data
R7 (C1: C8): Data
R8 (C1: C8) :data
R9: All Empty

And so on. Every little block signifies a different product, so in this example R1 and R2 are for one product, R4 is for another product, R6:R8 are for another product etc.

So I was wondering, can I have a function which gives each product its own worksheet and names the worksheet the value in Column 2? The number of rows for each product (and the number of products) is variable but the number of columns stay the same and there'll always be one gap between each product.

This isn't an essential can't-live-without thing, right now I'm just cutting and pasting, but it would be nice to have everything automated. Thanks for your help in advance, I'm using Office 2003 but please let me know if you need any more information,

AR

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Here is some code you could use

Please Login or Register  to view this content.

That you make them part of your 'tools' is OK. I think it is best to copy past the code in a module of a new workbook and send the complete Workbook.

There is not a big difference in SUB's and FUNCTIONS.
Functions as well as Subs can have arguments
FunctionA(arg1, arg2), SubA(arg1, arg2)

The difference is that a function return a variable and Sub's don't. To make User defined Functions this is a requirement as the "=" sign expects a returned value.