dividing sheets up

J

jason2444

I have a single sheet that is 300,000 lines,
is there an automated way to divide this into multiple sheets of 5,000 lines
or does it all have to be done with copy and past
 
F

Fred Smith

Copy and paste is one way.

The alternative would be to write a macro.

Regards,
Fred.
 
G

Gary Brown

You need to you a VBA macro for that.
See macro [CopyData2OtherWorksheets] below...
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown

'/===================================/
' Sub Purpose:
' start in cell A1 of the worksheet
' that you copy data from
'
'/===================================/
'
Public Sub CopyData2OtherWorksheets()
Dim dRowCount As Double, dCopyRows As Double
Dim dTotalRows2Copy As Double
Dim iWkstCount As Integer, i As Integer
Dim strWkstName As String

On Error GoTo err_Sub

'/- - - - V A R I A B L E S - - - - - /
dRowCount = -4999
dTotalRows2Copy = 300000 '# of rows to copy in total
dCopyRows = 5000 '# of rows to copy each time
'calc # of worksheets needed
iWkstCount = Int(dTotalRows2Copy / dCopyRows)
strWkstName = ActiveSheet.Name 'save name of starting wksht
'/- - - - - - - - - - - - - - - - - - /

Range("A1").Select

For i = 1 To iWkstCount
'start at original wksht that has the data
Sheets(strWkstName).Select
Sheets.Add
'rename the new worksheet
ActiveSheet.Name = "Wksht_" & i
'go back to the original worksheet
Sheets(strWkstName).Select
dRowCount = dRowCount + dCopyRows
'grab the data from the original worksheet
Rows(dRowCount & ":" & dRowCount + dCopyRows).Copy
'go to the new worksheet
Sheets("Wksht_" & i).Select
'copy the data to the new worksheet
ActiveSheet.Paste
Next i

exit_Sub:
On Error Resume Next
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Sub: CopyData2OtherWorksheets - Module: " & _
"Module1 - " & Now()
GoTo exit_Sub

End Sub
'/===================================/
 
D

Dave Peterson

Maybe you could use a macro.

I'd start looking at one of these references:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Or:

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

========
But I'd insert a helper column with a formula like:
=int(row()/5000)
and use that as the basis for the split.
 
Top