Excel 97 Worksheet Bug

[kluitna]

I am having problems with Excel 97. I have a macro that creates worksheets from a list. The problem is that it works just fine up to about 50 sheets then excel crashes . I know that you can have up to 256 worksheets in a workbook and I am no where near that. I have plenty of RAM to spare on my computer and I am running windows 2000. I read somewhere here there were bugs in 97's sheet numbering. Does anyone out there know if this is a bug also? And if so is there a patch for it or a way around it? My macro works very well for what I intend it for but this is quite disheartening when Excel crashes.

Any help is much appreciated
Paul

 

[kluitna]

I am still searching fro the answer to this question. have searched the forum and Microsofts website to no avail. Maybe there is no a fix for it out there. Has anyone else had this problem before?

 

[Jack in the UK]

Yes sheets can cause NAMES problems they are always refered to as sheet1 sheet2 3 4 5 regardless of what you rename them, VBA will allow calling by either name thou

Problem is sometimes say sheet is named 11111111111 thus errors my suggestion is re run code on another sheet and see, else alter teh name save sometime excel goes a tad mad on this one Excel XP is fine as far a si know, post your code and ill try on 97 2000 and xp for you, also remember memory is not always as we see it but win2K should stop pre NT5 issues

Jack

 

[kluitna]

Thanks Jack,

I noticed the it was naming the spread sheets strangely. My code is
Application.ScreenUpdating = False
Dim Itemnum As String
For i = 2 To 500
Itemnum = Worksheets(1).Cells(i, 1)
If Itemnum = blank Then End
Sheets(2).Select
Sheets(2).Copy After:=Sheets(i)
Sheets(i + 1).Select
Sheets(i + 1).Name = Itemnum
Worksheets(1).Select
Range("n1:n2").Select
Selection.Copy
Sheets(i + 1).Select
Range("c6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Worksheets(1).Select
Worksheets(1).Cells(i, 1).Select
Selection.Copy
Sheets(i + 1).Select
Range("c9").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Next i
Sheets(3).Select
Range("a1").Select
End Sub

I am not sure how to make it name the sheets differently. the tabs themselves are named from the list but the (name) is done by excel and I don;t know how to manipulate that trough code. Any ideas?

As for using 2000 or XP, I am screwed there. I work for the state of alaska and there is no plans on upgrading excel so I neeed to figure a way around this glitch.

You help is much appreciated.

Paul

 

[Jack in the UK]

Hi

Ok you run E97, ill test on my Sony Vaio Laptop now, speck is AMD800 128 blar blar running FULL XP's

Jack

 

[Jack in the UK]

Hi

Maybe its me but the code does not do a lot, looks like top missing, can you email a blank wkbk withthe working code in and ill test for you

I have PM you my email so send on.

Thanks
Jack

 

[kluitna]

Sure thing Jack. How do I go about emailing you. I can send it off right away appreciate the help.

Paul

 

[Jack in the UK]

Hi

As my post suggest i have PM you a email address

Jack

 

[XL-Dennis]

kluitna,

This is a well known problem so MS decided to publish an article in the knowledge base:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q236299&

OK Jack?

Please also note that MS at least admit that this is a problem (but *not* a bug) so we can at least disregard it as a feature :wink:

Kind regards,
Dennis
This message was edited by XL-Dennis on 2002-09-24 17:19

 

[kluitna]

Thanks for that info Dennis. Read the article. Will try there code atleast see if it can be intergrated in. A bummer is what it is, think I will have to push for 2000 here so I can solve this.