turns out the error is occuring ebcause the new work book i made workbook.add is made in compatability mode with excel 2003 which has a limit of 65536 rows. and the range im trying to paste has more rows than that. how do i add a workbook that is nto in compatability mode??

This code is copying a number of rows each tiem from a csv file. opening a work book ( add workbook) pasting the rows in . go back and deleting those rows. and keep doing this until all the rows from the original file are gone. Can you clarify how many rows and which rows you want each time. I'm not completely clear on what you are trying to do (I get the gist, just not the steps you are taking to reach that point). It may be hard to keep track of which is the active sheet, etc., so explicitly naming them might help in some of the confusion.
–
jonscaSep 3 '11 at 11:11

each time im trying to take row 9 to an x number of rows.paste it in another active file and then delete those rows from the original file. x is chosen by some calculation done earlier in the code. the files are not explicitly named because im going through hundreds of files. not just one and doing this for all the files with a For each files in a folder. hope this helps
–
ChaostryderSep 6 '11 at 15:27

turns out the error is occuring ebcause the new work book i made workbook.add is made in compatability mode with excel 2003 which has a limit of 65536 rows. and the range im trying to paste has more rows than that. how do i add a workbook that is nto in compatability mode??
–
ChaostryderSep 6 '11 at 18:17

2 Answers
2

Did you try without selecting each cell before pasting? (you don't need to select every cell before using it in vba) - and you don't need to copy / paste if you only want to copy the value (it is very very slow)

Dim ws as Worksheet
Set ws = ActiveSheet
For c = 1 To Round(z / x + 0.5)
Workbooks.Add
ActiveSheet.Range("A9").Value = ws.Rows("9:" & x).Value 'you shouldn't have to use ActiveSheet but i'll keep it to show the difference between ws and ActiveSheet
ActiveWindow.ActivateNext 'this is dangerous because you can't be sure which window will be activated next
Selection.Delete 'this is dangerous too. Can't you tell which row is this instead of using Selection? Is this macro user triggered?
Rows("1:8").Copy
ActiveWindow.ActivatePrevious 'same comment as above
Range("A1").Paste
Range("E3").Value = c
ActiveWorkbook.SaveAs Filename:=FileLocation2 & "\" & g & "-" & c _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ThisWorkbook.Save
ActiveWindow.Close
Next c

ActiveSheet.Range("A9").Value = ws.Rows("9:" & x).Value 'you shouldn't have to use ActiveSheet but i'll keep it to show the difference between ws and ActiveSheet
–
ChaostryderSep 6 '11 at 15:43

Please see my reply to jonscas comment. thanks for your coe but once it reaches the above part i will get the error # 7 out of memory. do you have any solutions for this?
–
ChaostryderSep 6 '11 at 15:44

yes really high value . im dealing wiht some worksheets with say 200 000 rows and 20 columns . but 15 of these 20 columns are parameters so i need to bring them down. so instead ill have like 200 000 x 15 rows which maxes out the limit for excel 2007 which is why im trying to split the file first. the x is part of my calculation earlier that predicts how many rows i split each files into. before the rows are brought down. and it will predict depending on number of rows and columns. but yes. x is a very high number
–
ChaostryderSep 6 '11 at 16:07

turns out the error is occuring ebcause the new work book i made workbook.add is made in compatability mode with excel 2003 which has a limit of 65536 rows. and the range im trying to paste has more rows than that. how do i add a workbook that is nto in compatability mode??
–
ChaostryderSep 6 '11 at 18:17

So i fixed the problem. it was a matter of adding the workbook. saving as .xlsx or whatever. and then reopening it before i paste anything to it. this takes the newly added workbook out of compatability mode.