Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
dividing sheets up
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
dividing sheets up
Copy and paste is one way.
The alternative would be to write a macro. Regards, Fred. "jason2444" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
dividing sheets up
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 '/===================================/ |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
dividing sheets up
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. jason2444 wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dividing Data between sheets in a workbook | Excel Worksheet Functions | |||
Dividing by zero | Excel Worksheet Functions | |||
My excel spread sheets are dividing all numbers input by 100. | Excel Worksheet Functions | |||
Dividing by zero | Excel Discussion (Misc queries) | |||
dividing a row | Excel Worksheet Functions |