Data Segregation based on number of line items using different sheets

[harinsh]

I’m looking vba code in order to segregate the data as per below requirement.

I have sheet1 which contains the data and required to copy this data till 90 line items including empty rows and needs to paste special in new sheet. This new sheet name should be changed to Setitem1 and it should continue similar like second set of 90 items and new sheet name should be Setitem2.

Example;
In sheet1 there is 180 line items including empty rows and first set of 90 line items should get copy to new sheet name called “Setitem1” and another 90 items should get copy to “setitem2” it goes till number of total line items get copy from the master data.

Hope can anyone help me with this.

Thanks you....

 

[harinsh]

1. Does that mean that the data copied to each sheet, including 'Setitem1', must not be blank in row 2 (headings in row1)?
A: Yes, it should not be blank because I have data like group of data you can refer in the below example data.

If so,

2. What do you mean by an "empty row"? Does that mean nothing in column A? Or nothing in all columns? Is there a set number of columns? How many?
A: No, I mean to say while copying data it should validate the last cell in the given column in case the below example its "C" if it finds blank then it should go next or previous cell which contains data and should end there itself. Irrespective of 90 columns or 100. Plz note that I can till go 100 not necessarily 90 because myself given 10 cells extra for format requirement.

3. Just checking that you don't care about blank rows throughout the data, or at the end - it is just blanks at the start that you don't want?
A: I want to ignore the data if cell contains blank and should copy nxt or previous data available cell. so, that I will get the data in next sheets with required information.

Example Data:
[TABLE="width: 256"]
<colgroup><col style="width:48pt" span="4" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]AAA
[/TD]
[TD="width: 64, align: right"]256585[/TD]
[TD="width: 64"]Store1[/TD]
[TD="width: 64, align: right"]25566666[/TD]
[/TR]
[TR]
[TD]BBB
[/TD]
[TD="align: right"]65975[/TD]
[TD]Store1[/TD]
[TD="align: right"]25566666[/TD]
[/TR]
[TR]
[TD]BBB
[/TD]
[TD="align: right"]454555[/TD]
[TD]Store1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AAA
[/TD]
[TD="align: right"]256585[/TD]
[TD]Store1[/TD]
[TD="align: right"]25566666[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD="align: right"]65975[/TD]
[TD]Store1[/TD]
[TD="align: right"]25566666[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]454555[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD="align: right"]256585[/TD]
[TD]Store1[/TD]
[TD="align: right"]25566666[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD="align: right"]65975[/TD]
[TD]Store1
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]CCC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD="align: right"]256585[/TD]
[TD]Store1[/TD]
[TD="align: right"]25566666[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD="align: right"]65975[/TD]
[TD]Store1[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]454555[/TD]
[TD]Store1[/TD]
[TD][/TD]
[/TR]
</tbody> [/TABLE]

Here if I found last cell in column "C" blank then it should ignore the entire three lines and it ends copying previous data....

 

[Peter_SSs]

I think we have a language problem as I'm afraid I can't understand from that what modifications I would need to make.

 

[harinsh]

Ok, let me explain bit more details......

I have data with frequent blank rows in between hence, the set of non-blank data we called it as one bill item. For example, from A2 to A5 we have data and immediate A6 has blank row hence the macro should copy A2 to A5. At the time of 90th row segregation the macro should understand the blank row if , it gets blank row in between say A87 to A91 then this set should not considered to next sheet then till A86 row should copy.

Hope you understand if not tell me I give me some more examples on this.

Thanks,