naming sheets

[Christian Galbavy]

Hello!

I think there is a general problem with excel. When I have several sheets
(over 20), and I want to rename a sheet, this takes about 30 seconds. I do
not know why and what is happening in the background, but it is a big
problem for my program. I use the "add" function to insert new sheets, and
the I use the "sheet.name" function to rename the inserted sheets. I have
more than 40 sheets in my workbook and as I said, the renaming-process takes
up to a minute.
When I insert a new sheet, my german excel version automatically names the
sheets like "Tabelle1", "Tabelle2", ...
Is there a function to set a certain name here, befor the insertion happens?
Or does anybody know, why the renaming function takes so much time?
Thanks for your help.

Regards
Christian Galbavy

 

[Snake Plissken]

Hello!

I think there is a general problem with excel. When I have several sheets
(over 20), and I want to rename a sheet, this takes about 30 seconds. I
do
not know why and what is happening in the background, but it is a big
problem for my program. I use the "add" function to insert new sheets,
and
the I use the "sheet.name" function to rename the inserted sheets. I have
more than 40 sheets in my workbook and as I said, the renaming-process
takes
up to a minute.
When I insert a new sheet, my german excel version automatically names
the
sheets like "Tabelle1", "Tabelle2", ...
Is there a function to set a certain name here, befor the insertion
happens?
Or does anybody know, why the renaming function takes so much time?
Thanks for your help.

If you have many sheets connected with each other , especially when u use
formulas such "lookup", "sum.if" etc. it might happend that after every
change of name of a sheet everything is recalculating.
Try to change option ( tools, options...) "recalculate automatically" into
"recalculate manually" then we'll see if any difference appear.
do not forget that if workbook is recalculated manually the results of any
formula is unchanged until u run "recalculate" (F9) option or before saving
of file.

 

[Bob Phillips]

Hi Christian,

Here is one way

Private iSheet As Long

Private Sub Workbook_NewSheet(ByVal Sh As Object)
iSheet = Worksheets.Count
Sh.Name = "Tabelle" & iSheet
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code