Split rows in excel

[CTl06]

Hello All,
I am not very good at excel. I hope someone can help me with this. My question is I have excel workbook that have a worksheet contains thousands and thousands rows of record. I need to write a macro that split every 60 rows into a new workbook with the worksheet contain the data. Is that possible to do this in Excel?

Thank you much in advance.
Joe

 

[AnotherHiggins]

Why on earth would you want to split up your data like that? It will be a nightmare to report on after it is split up.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[mintjulep]

The macro recorder is your friend.

 

[SkipVought]

Let me echo John -- WHY chop up your data?

Skip,

_[red][/red]

 

[CTl06]

The reason i want to chop up the data is because every sixty rows is a set of record pertain to an individual. I want to split ups so I can send it to the individual with excel spreadsheet just have their data.

 

[SkipVought]

Use the AutoFilter.

Skip,

_[red][/red]

 

[AnotherHiggins]

->Use the AutoFilter

.... assuming that you have the employee name/ID in a column that you can filter on.

If you don't, or need more help, please provide more information about your data. What fields DO you have? Is there ever a time where an employee won't have exactly 60 records (maybe a new-hire only have 15)?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[CTl06]

there always sixty row regargless new or old. The reason I don't want to use the autofilter because I want to split into indidividual workbook. each workbook will contain 60 rows of record.

i hope this makes sense.

 

[SkipVought]

You have ALL the source data in a DATABASE (Workbook or Access)

Each user's front-end workbook has their ID (on a hidden sheet) that is used in a Parameter Query to return that user's data from the database automatically each time the user's workbook opens.

That way, there is no continuing distribution issue -- just load the database with current data.

Skip,

_[red][/red]

 

[WinblowsME]

Sub Split_60()
   Dim app_path As String, dir_name As String, file_name As String, i As Long, last_row As Long
   
   app_path = Application.ActiveWorkbook.Path
   dir_name = "\Workbooks\"
   last_row = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
   
   Application.DisplayAlerts = False
   
   For i = 1 To last_row Step 60
      file_name = "Start Line " & i
      
      Rows(i & ":" & i + 59).Copy
      Workbooks.Add
      Workbooks(Workbooks.Count).Sheets(1).Paste
      Workbooks(Workbooks.Count).Sheets(1).Range("A1").Select
      Workbooks(Workbooks.Count).SaveAs app_path & dir_name & file_name & ".xls", FileFormat:=xlExcel9795
      Workbooks(Workbooks.Count).Close
   Next i
End Sub