Problems creating large numbers of sheets

[Johnny2combs]

Hi, I have a spreadsheet that contains two sheets the first (called "Index") is to have a list of data as entered by the user. The second sheet (called "Blank") is a blank template sheet.

What i want the macro to do is upon entering all the data i want it to create a sheet for every piece of data, i want the sheets to be in the order the data is entered and i want the sheets to be the same name as the data.

The code i have written is:

Sub CreateSheets()

Dim row As Integer

Application.ScreenUpdating = False

Sheets("Index").Select

row = 4

Do Until Cells(row, 2) = "" 'to go down data collumn untill it reaches bottom

Sheets("Blank").Copy After:=Worksheets(Worksheets.Count) 'copy blank sheet and move to end

ActiveSheet.name = Sheet1.Cells(row, 2) 'rename to same value as data

row = row + 1

Sheets("Index").Select

Loop

Application.ScreenUpdating = True

End Sub

This works fine upto about 50 sheets but any more than that and i get a 'Run time error 1004, Copy method of worksheet class failed'.

The:

Sheets("Blank").Copy After:=Worksheets(Worksheets.Count)

is the line which is highlighted as the problem.

Can anyone spot what i'm doing wrong?

Thanks, Johnny

[Mavyak]

The number of sheets allowed in a workbook is limited by your computer's available memory. So, how much RAM ya got?

[Johnny2combs]

2 Gig, I don't think thats the problem. I've also noticed that if i run it to create say 40 sheets (which it is fine with) then delete them all and try it again it crashes then as well. :s I'm at a loss.

[Mavyak]

I'm at a loss. I copied your code to a new workbook and put two sheets in there named Index and Blank. I then enumerated column B down to 86 and ran your code. I had no errors and 83 sheets were created named 4 through 86. Perhaps you have other applications running that are taking a bite out of your RAM? Even if that were the case, I would think that page-filing would kick in. When the code breaks, do you select "End"? Can you select Debug and then resume your code by hitting F5? If so, does it continue without incident? What I'm wondering is, is the code hiccupping because Excel has to wait for your hard-drive when it switches over to page-filing after the RAM is exhausted?

[darthobra]

How many sheets will you potentially be creating?
--Also, do you have any code for Workbook_SheetChange, _SheetDeactivate, _SheetActivate etc.?

[Johnny2combs]

Thanks for the help, the problem in the end turned out to be a known bug with in Excel, no wonder i couldn't fix it! - I'd post the link to the microsoft article about it but the forum wont let me as my post count is too low.

To get around the bug i saved the 'blank' sheet as a template and rather than copying the sheet i opened the template.

Cheers anyway guys,

Johnny

[Mavyak]

Interesting. Can you post the link as a URL formatted so it will bypass the low-post filter (e.g. "website dot com")?

[Johnny2combs]

yeah it's:

support dot microsoft dot com / kb / 210684

Going by what the article says it would appear the number of sheets is random depending on the computer, which would explain how you managed 80+ were i could only do 50+