Break out data into multiple worksheets using a macro.

[Question_123]

I posted this yesterday, but I think I was unclear about what my goals
were. Simply put I have a workbook. Its one big sheet of data. I
would like to break this data out into multiple sheets. Ex. (In my
workbook of data there are 100 records. I want to break out that 100
records into 4 worksheets (25 records per sheet.) The macro should be
able to grow to match the number of inital records. If there are 125
records there should be 5 sheets created etc. Also the first record of
each 25 records contains a unique value. I would like to use this
value as the worksheet.name if its possible. I am not used to excel,
or its lingo, but I clearly can use some help. Thanks.

 

[Nigel]

Try this .........
Assumptions included in what follows
The the first sheet of the workbook has the data on it.
The first row of data starts in row 1
The first column is A, which contains the Sheet Name in every 25th row

Public Sub Split()
Dim LastRow As Long, ShToAdd As Integer, ShAlready As Integer
' find last data row
LastRow = Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row
' work out how many sheets to add based on multiples of 25
ShToAdd = Int(LastRow / 25) + 1
Dim i As Integer, r As Integer
'loop though adding sheets, naming them and copy-paste the data
For i = 1 To ShToAdd
r = (i - 1) * 25 + 1
Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Worksheets(1).Cells(r, 1).Text
Worksheets(1).Rows(r & ":" & r + 24).Copy
Range("A1").Select
ActiveSheet.Paste
Next i
End Sub

Cheers
Nigel