OK...my code has gone through some changes and I am almost there. With
regards to the known issue with copying worksheets, I tried using the
suggested code, and everything works just fine until the point when I save,
close and reopen. I can't get my workbook to reopen to complete the loop.
Here is the code that I am currently using:

Sheets("Data").Select
Dim iBook As Workbook
Dim iWorkSheet As Worksheet
Dim iRowCounts As Integer
Dim iRowCount As Integer

Set iBook = Application.ActiveWorkbook
Set iWorkSheet = ActiveWorkbook.ActiveSheet
iRowCounts = iWorkSheet.UsedRange.Rows.Count

For iRowCount = 2 To iRowCounts
Sheets("Sheet1").Select
Sheets("Sheet1").Copy Befo=Sheets(1)
Range("C8:D12,C15:D17,C20:D22,C25:D27,A3:H3").Sele ct
Range("A3").Activate
Selection.Replace What:="2", Replacement:=iRowCount
Range("A1").Select
ActiveSheet.Name = ActiveSheet.Range("A3").Value

If iRowCount Mod 100 = 0 Then
iBook.Close SaveChanges:=True
Set iBook = Nothing
Set iBook = Application.Workbooks.Open("c:\TEST1.xls")
End If

Next iRowCount

"Jim Thomlinson" wrote:

A couple of things I see....
1. Where do you define total. You are counting to some number called total
without defining it for us.

2. There is a known issue with copying worksheets...
http://support.microsoft.com/default...84&Product=xlw

--
HTH...

Jim Thomlinson


"MB" wrote:

I have a macro that is supposed to count how many rows are active in the
current worksheet and generate a new sheet for each record. For some reason,
it generates 55 sheets and stops. When I have 39 records, it generates too
many and when I have 307 records, it doesn't generate enough. What's up with
the 55?

Here's the code:

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
total = Selection.Rows.Count
Range("A1").Select
counter = 1
Do Until counter = total
counter = counter + 1
Sheets("Sheet1").Select
Sheets("Sheet1").Copy Befo=Sheets(1)
Range("C8:D12,C15:D17,C20:D22,C25:D27,A3:H3").Sele ct
Range("A3").Activate
Selection.Replace What:="2", Replacement:=counter
Range("A1").Select
Loop


Please help...signed...Confused!!!