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

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" a écrit dans le
message de news: ...

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

Papou,
Thanks for the reply, however my workstation has 1 gig of ram and the
Workbook in question has never grown past 353KB when it had 150 of
these sheets. (they are fairly simple)


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=529613