Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split data into multiple worksheets by row count
Hi,
I have a very large data list which I need to run some formula on, however because the data has over 200,000 rows when the formula is ran the processor grinds to a halt. Is there any way using vba that the rows can be split into worksheets for say every 10,000 rows of data? Any help is greatly appreciated. Thanks Stuart |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split data into multiple worksheets by row count
Stuart
Try this, change srchsheet to the sheet with your data and rnglen to the size of block to copy (Iused 24 rows. Note also the ofset statement (curently 25) resize this to how many columns you want to copy Sub Lime() Dim srcSheet As String Dim RngLen As Long RngLen = 25 'Change to suit srcSheet = "Sheet1" 'Change to suit For x = 1 To 100 Step RngLen Sheets(srcSheet).Range("A" & x & ":A" & x + RngLen - 1).Resize(, 25).Copy Worksheets.Add After:=Worksheets(srcSheet) ActiveSheet.Range("A1").PasteSpecial Next End Sub Mike "Stuart" wrote: Hi, I have a very large data list which I need to run some formula on, however because the data has over 200,000 rows when the formula is ran the processor grinds to a halt. Is there any way using vba that the rows can be split into worksheets for say every 10,000 rows of data? Any help is greatly appreciated. Thanks Stuart |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split data into multiple worksheets by row count
Here are some suggestions for large databases
1) Use Access rather then excel. Access is designed tohandle very large amounts of data than excel 2) Use CSV files rather than XLS if you don't need the formating. 3) Rather than put formulas in the worksheet use evaluate results = evaluate("Sum(A1:A200000)") "Stuart" wrote: Hi, I have a very large data list which I need to run some formula on, however because the data has over 200,000 rows when the formula is ran the processor grinds to a halt. Is there any way using vba that the rows can be split into worksheets for say every 10,000 rows of data? Any help is greatly appreciated. Thanks Stuart |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split data into multiple worksheets by row count
Thanks Mike H - your suggestion works perfectly.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Split worksheet into multiple worksheets | Excel Programming | |||
Split a worksheet into multiple worksheets of the same Excel file | Excel Programming | |||
How to split up one spreadsheet into multiple worksheets | Excel Discussion (Misc queries) | |||
split results to multiple worksheets | Excel Worksheet Functions | |||
Data from One Worksheet split into Multiple Worksheets | Excel Programming |