splitting excel workbook into seperate sheets depending on number of rows

Hello,
I have an excel workbook with over 40k rows with first 7 rows that as header. I would like to have a macro that divides this data into seperate worksheets. Details are:
1)Each worksheet should have 35 rows of data from first worksheet and header (first 7 rows in the first raw data sheet)
2) First raw data sheet should stay as it is
3)other worksheets should be named 1,2,3,4 ..etc
4)macro should stop when there all data is copied to different worksheets.

I really appreciate your help

Thanks, but it is not working. I run it just like the other macros in the document but it does not do anything

thanks, but this only creates one worksheet,renames it but there is no data in it

Both Norie's and mine work in a trial workbook. Mind you, I only tried with just over 4,000 entries, not 40,000 but that should not differ.

Hi, could you please attach the trial workbook? thx

here is the sample file, I reduced the number or rows

As Norie mentioned, a minor tweak.

Many thanks to both, its now working perfectly I just have one more issue to tackle and would really appreciate your help on it. After splitting I need to sum the balance and write it on the sheet and also to the next sheet.How can I do it with a macro?
Here are the details.
Go to sheet "1"
Copy the number in G44
Go to next sheet (sheet "2")
Paste as values to cell G45
This should go on until sheets end.
Thanks again

If the balance is in Column G, the this might do it.
Try it on a copy of your workbook please.

Please Login or Register  to view this content.