MrExcel Publishing
Your One Stop for Excel Tips & Solutions

why is the name of sheet 411111111?


Posted by Karol on July 02, 2001 2:52 AM

In Excel 97 I copy a sheet in the same file through VBA
macro. Name of the new sheet in the active workbook is
date, but name of the sheet as a Microsoft Excel Object
is still *Sheet*. I´d expect that name of the new
object will be created like this:

original *Sheet4* copy, new sheet *Sheet5*

but the new sheet is named this way

original *Sheet4* copy, new sheet *Sheet41*

and if the original sheet is copying ten times, the name
of the tenth sheet is *Sheet41111111111*. After closing
the file and reopening the sheets´names are still
strange. This is serious problem because I can make
only about 30 sheets in the file this way.
If the file is saved as Excel 5.0/95 the objects´names
are O.K. but it will generate other problems with
the old spreadsheet format.
Is there any help, please?

Karol


Posted by mseyf on July 02, 2001 12:08 PM

just out of curiosity, what happens if you try to add more than 30 sheets? the number of sheets should only be limited by the amount of the PC's memory. Also, I'm curious why the name that excel assigns to a sheet is makes any difference. There are a number of different ways to refer to a sheet other than the 'Excel Object' name (unless I suppose you're trying to manipulate the excel object from an application other than excel). You could always go in and change the names of the sheets through the properties window.

Just wondering,

Mark

Posted by Ivan F Moala on July 02, 2001 6:45 PM


In Excel 97 there is a problem in copying sheets
This has been fixed in excel2000.
Have a look @ http://support.microsoft.com/support/kb/articles/Q177/6/34.ASP?LN=EN-US&SD=gn&FR=0&qry=copy%20sheets&rnk=20&src=DHCS_MSPSS_gn_SRCH&SPR=XLW97

The error relates to excel adding 1's to the codes
sheet object so that copying past 30 produced
4111111111111111111 etc. Evidently this cause
a page fault error.
The above page suggests a work around which is
in effect just a cells.copy ......you may have
noticed that on some code this method is used
instead of a sheets copy for doing mass copies.


Ivan

Posted by Karol on July 03, 2001 12:31 AM

the name of sheet is not 411111111 anymore

Ivan,

thank you for tip. It does seem it works through
sheets.add, cells.copy and pagesetup to make a precise copy of sheet. The names of sheets like
X1111 has disappeared.

Karol

Posted by Karol on July 03, 2001 12:56 AM

Excel 97 shuts down

I refer to sheets inside the application, it works, of course, and I don´t need any object name. But if you´ve got from the application a copy of sheet (object) named with about thirty 1-s Excel 97 always shuts down. Yes, I can rename it in the properties window, but I need other people to work with a file automatically by simple clicking and waiting for results.

Karol