Hello
I may be wrong but you may have reached the limit in the number of sheets
you can create.
This is a specification in Excel and related to the available memory of your
PC.

HTH
Cordially
Pascal

"Casey" <Casey.25qta2_1144159355.9013@excelforum-nospam.com> a écrit dans le
message de news: Casey.25qta2_1144159355.9013@excelforum-nospam.com...
>
> Hi everybody,
> I have a routine that adds a new worksheet for each value in a Dynamic
> Named Range. The range points to the correct data and the routine works
> fine to a point, but always seems to stop short of creating all the
> sheets. The named range currently is 220 cells and starts with 001 and
> ends at 391. Obviously there are missing numbers in the sequence, but
> the numbers are sorted ascending. Any ideas why the rountine would just
> stop part way through??
>
> Here is the code:
>
> Option Explicit
>
> Sub CreateRoomSheets()
>
> Dim WB As Workbook
> Dim Sht As Worksheet
> Dim rng As Range
> Dim rCell As Range
>
>
> Set WB = ThisWorkbook
> Set Sht = WB.Sheets("Room Blank")
> Set rng = WB.Sheets("Room List").Range("RoomNo")
>
>
> On Error GoTo RET
> Application.ScreenUpdating = False
>
>
> For Each rCell In rng.Cells
> With rCell
> Sht.Copy After:=WB.Sheets(WB.Sheets.Count)
> ActiveSheet.Name = .Value
> End With
> Next rCell
>
> RET:
> Application.ScreenUpdating = True
> End Sub
>
>
> --
> Casey
>
>
> ------------------------------------------------------------------------
> Casey's Profile:
> http://www.excelforum.com/member.php...fo&userid=4545
> View this thread: http://www.excelforum.com/showthread...hreadid=529613
>