How to split the set of data into specify number of rows on multiple sheets

[noppph]

I'm having a issue to split my data set into multiple sheets with specify number of rows with their headers from input sheet.


I want to use Excel-VBA to solve this, Thank you...


Example: My input dataset
[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Name[/TD]
[TD="class: xl65, width: 64"]Cost[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dew[/TD]
[TD="class: xl65, align: right"]12000[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dew[/TD]
[TD="class: xl65, align: right"]12001[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dew[/TD]
[TD="class: xl65, align: right"]12002[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dew[/TD]
[TD="class: xl65, align: right"]12003[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dew[/TD]
[TD="class: xl65, align: right"]12004[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dew[/TD]
[TD="class: xl65, align: right"]12005[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dew[/TD]
[TD="class: xl65, align: right"]12006[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dew[/TD]
[TD="class: xl65, align: right"]12007[/TD]
[/TR]
[TR]
[TD="class: xl65"]Aun[/TD]
[TD="class: xl65, align: right"]987000[/TD]
[/TR]
[TR]
[TD="class: xl65"]Aun[/TD]
[TD="class: xl65, align: right"]987001[/TD]
[/TR]
[TR]
[TD="class: xl65"]Aun[/TD]
[TD="class: xl65, align: right"]987002[/TD]
[/TR]
[TR]
[TD="class: xl65"]Aun[/TD]
[TD="class: xl65, align: right"]987003[/TD]
[/TR]
[TR]
[TD="class: xl65"]Aun[/TD]
[TD="class: xl65, align: right"]987004[/TD]
[/TR]
[TR]
[TD="class: xl65"]Aun[/TD]
[TD="class: xl65, align: right"]987005[/TD]
[/TR]
[TR]
[TD="class: xl65"]Aun[/TD]
[TD="class: xl65, align: right"]987006[/TD]
[/TR]
[TR]
[TD="class: xl65"]Aun[/TD]
[TD="class: xl65, align: right"]987007[/TD]
[/TR]
[TR]
[TD="class: xl65"]Aun[/TD]
[TD="class: xl65, align: right"]987008[/TD]
[/TR]
[TR]
[TD="class: xl65"]Fon[/TD]
[TD="class: xl65, align: right"]98752.25[/TD]
[/TR]
[TR]
[TD="class: xl65"]Fon[/TD]
[TD="class: xl65, align: right"]98753.25[/TD]
[/TR]
[TR]
[TD="class: xl65"]Fon[/TD]
[TD="class: xl65, align: right"]98754.25[/TD]
[/TR]
[TR]
[TD="class: xl65"]Fon[/TD]
[TD="class: xl65, align: right"]98755.25[/TD]
[/TR]
[TR]
[TD="class: xl65"]Fon[/TD]
[TD="class: xl65, align: right"]98756.25[/TD]
[/TR]
[TR]
[TD="class: xl65"]Fon[/TD]
[TD="class: xl65, align: right"]98757.25[/TD]
[/TR]
[TR]
[TD="class: xl65"]Fon[/TD]
[TD="class: xl65, align: right"]98758.25[/TD]
[/TR]
[TR]
[TD="class: xl65"]Fon[/TD]
[TD="class: xl65, align: right"]98759.25[/TD]
[/TR]
</tbody>[/TABLE]


These are the output table that i'm looking for.
Sheet1, Sheet2, etc are the output sheets that i want to get. **Each of my sheets can contain only 6 rows(header included).

Sheet1:
[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Name[/TD]
[TD="class: xl65, width: 64"]Cost[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dew[/TD]
[TD="class: xl65, align: right"]12000[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dew[/TD]
[TD="class: xl65, align: right"]12001[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dew[/TD]
[TD="class: xl65, align: right"]12002[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dew[/TD]
[TD="class: xl65, align: right"]12003[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dew[/TD]
[TD="class: xl65, align: right"]12004[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2:
[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Name[/TD]
[TD="class: xl65, width: 64"]Cost[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dew[/TD]
[TD="class: xl65, align: right"]12005[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dew[/TD]
[TD="class: xl65, align: right"]12006[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dew[/TD]
[TD="class: xl65, align: right"]12007[/TD]
[/TR]
[TR]
[TD="class: xl65"]Aun[/TD]
[TD="class: xl65, align: right"]987000[/TD]
[/TR]
[TR]
[TD="class: xl65"]Aun[/TD]
[TD="class: xl65, align: right"]987001[/TD]
[/TR]
</tbody>[/TABLE]

 

[DILIPandey]

Hi nopph,

use below macro:-

Sub datasplit()
'created by DILIPandey on 23-Feb'16
Range("a2").Select
i = 2
n = 0
s = ActiveSheet.Name
While ActiveCell.Value <> ""
    Range(Range("a" & i * 4 - 6 + n), Range("a" & i * 4 - 6 + n).Offset(4, 1)).Copy
    Sheets.Add
    Range("a2").PasteSpecial
    Range("a1").Value = "Name"
    Range("b1").Value = "Cost"
        i = i + 1
        n = n + 1
    Sheets(s).Select
    Range("a" & i * 4 - 6 + n).Select
Wend
End Sub

Regards,
DILIPandey

 

[noppph]

Hi nopph,

use below macro:-

Sub datasplit()
'created by DILIPandey on 23-Feb'16
Range("a2").Select
i = 2
n = 0
s = ActiveSheet.Name
While ActiveCell.Value <> ""
    Range(Range("a" & i * 4 - 6 + n), Range("a" & i * 4 - 6 + n).Offset(4, 1)).Copy
    Sheets.Add
    Range("a2").PasteSpecial
    Range("a1").Value = "Name"
    Range("b1").Value = "Cost"
        i = i + 1
        n = n + 1
    Sheets(s).Select
    Range("a" & i * 4 - 6 + n).Select
Wend
End Sub

Regards,
DILIPandey

Dear DILIPandey,

Thank you so much, it's working.
Due to i'm a newbie, could you please explain about the "* 4 - 6" inside the code.
I guess that 6 is maximum rows per sheet.

Thank you.

 

[DILIPandey]

You are welcome.

(i * 4 - 6) is there to tell Excel about the starting position of the range to be copied and i is a running number here.
Run the code in break mode (F8) and move your cursor on these values to understand the logic completely.

Regards,
DILIPandey

 

[noppph]

Thank you DLIPandey,

the F8(Code break mode) is give me better understanding about your code,

...Since in my real life have to adapt your code to work with around 1000000 records(Excel2013) and save them as Excel97-2003(6XXXX rec limited).

Could you please suggest me how to edit your code to split the data into 60000 records per sheet(i guess it's about the "Offset") ?

Thank you and sorry for asking too much

 

[DILIPandey]

Yes... you are right.
Offset(4, 1) is the place where you need to update.
So if you need to consider 60000 rows,
Offset(4,1) will change to Offset(599998,1)

Why 599998 ? because 60000-2 = 599998 where 2 is the starting row to copy the data (row 1 being heading).


Regards,
DILIPandey

 

[noppph]

Dear DILIPandey

Thank you so much for your answers & explanations.
you are such a great teacher

Kind regards,
Noppph

 

[DILIPandey]

You are welcome noppph.

Cheers


Regards,
DILIPandey