sancarn

    Dim rng as range: set rng = Sheet1.UsedRange
    Dim rows as Long: rows = rng.rows.count
    Dim cols as long: cols = rng.columns.count
    Const SIZE as long = 500
    For i = 2 to rows step SIZE
      Dim wb as workbook: set wb = workbooks.add()
      wb.sheets(1).range("A1").resize(1, cols).value = rng.resize(1).value
      wb.sheets(1).range("A2").resize(SIZE,cols).value = rng.offset(i-1).resize(SIZE).value
      Call wb.SaveAs("C:\Temp\" & i & ".xlsx")
    next