Depends on the sheet. ISTR numbers like 29 or 39. Microsoft has actually
issued a KB article with a workaround, which goes like this: if you get the
error, save and close the workbook, reopen it, and continue.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"papou" wrote in message
...
Hello Jon
Thanks for that, so its definitely not a memory issue.
Is the error always occuring above 26 copies ?

Cordially
Pascal

"Jon Peltier" a écrit dans le message de
news: ...
This problem actually occurs on most any computer that I've used. The
problem is the worksheet.copy command. There are a couple workarounds:

1. Create a blank sheet, then use copy/paste (special) to recreate the
copied sheet.
2. Create a template of the sheet to be copied, and insert a new sheet
based on the template.

The second is better, because it makes a complete copy.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"papou" wrote in message
...
Hello Janis
Cannot be too definite but it looks like it is the computer your are
working on: it has probably reached the maximum available memory
ressource and cannot go on.

HTH
Cordially
Pascal

"Janis R" a écrit dans le message de news:
...
I need this macro to be a little more robust. It works but only up to
26. Could it be the computer I'm on? If I change the range in the
patient's sheet to only c1:c26 this macro works without a hitch. If I
add any more rows it crashes. i need it to copy all the sheets and
get this done. It is rather urgent. Can I use an array instead of a
loop. Any ideas?
many thanks,

Dim lngLastRow As Long
Dim ws As Worksheet
Dim wb As Workbook
Dim c As Range
Dim rng As Range
Dim sStr As String, Lname As String
Dim inputDate As Date

Set wb = ThisWorkbook
Set ws = wb.Worksheets("patients")
Set ws = ThisWorkbook.Worksheets("patients")

'lngLastRow = Cells(Rows.Count, "c").End(xlUp).Row
' lngLastRow = ws.Cells.Find(What:="*", After:=ws.Range("C1"), _
SearchDirection:=xlPrevious).Row
lngLastRow = ws.Cells.Find("*", SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row
Debug.Print lngLastRow
Set rng = ws.Range("C1:C" & lngLastRow)

inputDate = InputBox("Enter a date:", "Date", Date)
For Each c In rng.Cells

wb.Sheets(2).Copy befo=wb.Sheets(2)
Set ws = wb.Sheets(2)

ws.Range("T5") = inputDate
sStr = c
Lname = Mid(sStr, InStr(1, sStr, " ") + 1)
ws.Name = Lname
Next c
End Sub