Well.., that's not going to work either! Here's my version of
Isabelle's approach...
Option Explicit
Sub ParseSheetToWorkbooks()
Dim wkbSource As Workbook, wkbTarget As Workbook
Dim x%, i%, lWksCount&, lCalcMode&
Dim bEventsEnabled As Boolean
Const sPath As String = "C:\temp" '//edit to suit
Set wkbSource = ActiveWorkbook
With Application
lWksCount = .SheetsInNewWorkbook: .SheetsInNewWorkbook = 1
lCalcMode = .Calculation: .Calculation = xlCalculationManual
bEventsEnabled = .EnableEvents: .EnableEvents = False
.ScreenUpdating = False
End With 'Application
For i = 1 To 501 Step 100
x = x + 1
Set wkbTarget = Workbooks.Add
If i = 1 Then
wkbSource.ActiveSheet.Rows(i & ":" & i + 99).Copy _
wkbTarget.ActiveSheet.Rows(1)
Else
With wkbSource
.ActiveSheet.Rows(1).Copy wkbTarget.ActiveSheet.Rows(1)
.ActiveSheet.Rows(i & ":" & i + 99).Copy _
wkbTarget.ActiveSheet.Rows(2)
End With 'wkbSource
End If
With wkbTarget
.SaveAs sPath & "\gubbins" & x & ".xls": .Close
End With 'wkbTarget
Next
With Application
.CutCopyMode = False
.SheetsInNewWorkbook = lWksCount: .Calculation = lCalcMode
.EnableEvents = bEventsEnabled: .ScreenUpdating = True
End With 'Application
Set wkbSource = Nothing: Set wkbTarget = Nothing
End Sub 'ParseSheetToWorkbooks
--
Garry
Free usenet access at
http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc