causing error in excel 2007 . how do i add work book not in compatability mode

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??

For c = 1 To Round(z / x + 0.5)
Rows("9:" & x).Select
Selection.Copy
Workbooks.Add
Range("A9").Select
ActiveSheet.Paste
'Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.ActivateNext
Selection.Delete
Rows("1:8").Copy
ActiveWindow.ActivatePrevious 
Range("A1").Select
ActiveSheet.Paste
'Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Rang开发者_JAVA技巧e("E3") = c 
    ActiveWorkbook.SaveAs Filename:=FileLocation2 & "\" & g & "-" & c _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
ThisWorkbook.Save
ActiveWindow.Close
Next c

---

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

---

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.

Thank you everyone for your help!