Automatic creation of worksheets

[dave]

Does anyone have any idea how to accomplish the following using a
macro? The first sheet has 'n' number of rows. I want to
automatically create a seaparate worksheet for each block of 25 rows.

The 2nd worksheet would contain the data from rows 1-25 of the 1st
worksheet.

The 3rd worksheet would contain the data from rows 26-30 of the 1st
worksheet.

etc. until the last row of the 1st worksheet

The name of each worksheet would be the contents of column B1, hyphen,
contents of column B25.

Thanks.

 

[Guest]

Try this macro.

Sub SplitWorksheet()

FirstSheetname = ActiveSheet.Name
Lastrow = Range(Cells(1, 1), Cells(Rows.Count, 1)).End(xlDown).Row

For i = 1 To Lastrow Step 25
NewSheetName = Sheets(FirstSheetname). _
Range("B1").Offset(rowoffset:=i - 1, columnoffset:=0) + "-"
NewSheetName = NewSheetName + Sheets(FirstSheetname).Range("B25"). _
Offset(rowoffset:=i - 1, columnoffset:=0)
Sheets.Add
ActiveSheet.Name = NewSheetName
Sheets(FirstSheetname).Rows("1:25"). _
Offset(rowoffset:=i - 1, columnoffset:=0).Copy _
Destination:=Worksheets(NewSheetName).Range("A1")

Next i


End Sub

 

[dave]

That is exactly what I wanted. Thank you very much.