I am having some data in A COLUMN and the data is available in 5000 cells (A
COLUMN CELLS). Now I want a macro that should take the first 200 CELLS data
which is available in A COLUMN to a NEW WORK SHEET. The next 200 should be
created in another WORK SHEET like this all the 5000 data in A COLUMN should
be created as a NEW WORKSHEET as 200 each in all NEW WORK SHEETS.

At the same time I would like to inform you that the data in A COLUMN cells
will not be a stable one and it will vary like 2000 Or 3000 like this. At
the same time I assure you that there will not be any blank cells in between
that 2000 or 3000 or 5000 cells. So please create a macro that should itself
select the range from starting to ending of the A COLUMN data and it should
split the cells as 200 each in every NEW WORK SHEET.

Finally in every end of the NEW SHEET that is in cell no 201 the macro
should a show the Value as €œabove data is for 200 cells€�.

I hope I have explained it correctly and thanks in advance.

Please give a macro for this because I need it urgently.

Thank you,

TGV

Hi,

Alt+F11 to open VB editor. Right click 'ThisWorkbook'and insert module and
paste this in and run it. It assumes the source data are in Sheet1

Sub sonic()
lastrow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow Step 200
ActiveWorkbook.Worksheets.Add After:=Sheets("Sheet1")

Sheets("Sheet1").Rows(i & ":" & WorksheetFunction.Min(i + 199, lastrow)).Copy
ActiveSheet.PasteSpecial

Next
End Sub

Mike

"TGV" wrote:

I am having some data in A COLUMN and the data is available in 5000 cells (A
COLUMN CELLS). Now I want a macro that should take the first 200 CELLS data
which is available in A COLUMN to a NEW WORK SHEET. The next 200 should be
created in another WORK SHEET like this all the 5000 data in A COLUMN should
be created as a NEW WORKSHEET as 200 each in all NEW WORK SHEETS.

At the same time I would like to inform you that the data in A COLUMN cells
will not be a stable one and it will vary like 2000 Or 3000 like this. At
the same time I assure you that there will not be any blank cells in between
that 2000 or 3000 or 5000 cells. So please create a macro that should itself
select the range from starting to ending of the A COLUMN data and it should
split the cells as 200 each in every NEW WORK SHEET.

Finally in every end of the NEW SHEET that is in cell no 201 the macro
should a show the Value as €œabove data is for 200 cells€�.

I hope I have explained it correctly and thanks in advance.

Please give a macro for this because I need it urgently.

Thank you,

TGV

Thank you very much mike! It's working fine.....

"Mike H" wrote:

Hi,

Alt+F11 to open VB editor. Right click 'ThisWorkbook'and insert module and
paste this in and run it. It assumes the source data are in Sheet1

Sub sonic()
lastrow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow Step 200
ActiveWorkbook.Worksheets.Add After:=Sheets("Sheet1")

Sheets("Sheet1").Rows(i & ":" & WorksheetFunction.Min(i + 199, lastrow)).Copy
ActiveSheet.PasteSpecial

Next
End Sub

Mike

"TGV" wrote:

I am having some data in A COLUMN and the data is available in 5000 cells (A
COLUMN CELLS). Now I want a macro that should take the first 200 CELLS data
which is available in A COLUMN to a NEW WORK SHEET. The next 200 should be
created in another WORK SHEET like this all the 5000 data in A COLUMN should
be created as a NEW WORKSHEET as 200 each in all NEW WORK SHEETS.

At the same time I would like to inform you that the data in A COLUMN cells
will not be a stable one and it will vary like 2000 Or 3000 like this. At
the same time I assure you that there will not be any blank cells in between
that 2000 or 3000 or 5000 cells. So please create a macro that should itself
select the range from starting to ending of the A COLUMN data and it should
split the cells as 200 each in every NEW WORK SHEET.

Finally in every end of the NEW SHEET that is in cell no 201 the macro
should a show the Value as €œabove data is for 200 cells€�.

I hope I have explained it correctly and thanks in advance.

Please give a macro for this because I need it urgently.

Thank you,

TGV