Split data into multiple worksheets by row count

[Stuart]

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

 

[Mike H]

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

 

[Joel]

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]

Thanks Mike H - your suggestion works perfectly.