correction:
If i manually rename the sheet to some number, it takes 2-3 seconds to
register the change.
some names get registered instantly (manually) and some take time.
guess it the algorithm...

"Rishi" wrote:

Hi Pat.
Yea. If i use any number below 1000 (where in 1000 was added to the i just
for the heck of it) it runs slow.
If i use 10000, it runs fast... but after using 10000, if i use 1000, it
runs slow again. (nearly 7-10 mins)
(i know im sounding weird here.. :P but basically sometimes it runs fast,
sometimes slow )
I think its an issue with the algorithm that excel uses to check wether the
sheetname already exists . (Just a guess)
By the way i do have the sheets linked to some summary sheets etc, but that
doesnt seem to be the problem.
cos if i rename the sheet manually to no matter what name, it gets changed
quickly.
As for my 2nd loop. It takes a lot of time no matter what i do. [got 1 Gig
RAM btw]
time readings for my 2nd loop:
Debug.Print t1, t2, t2 - t1
39611.29 40797.21 1185.922
20 mins is it?

-Rishi Doshi

"Patrick Molloy" wrote:

Excel 2003, Windows XP

I can't see any issue

seemed quite fast for me. run #4 was below, & I added somethign each run.
I had a new workbook with 500 sheets

when I added a further 500 sheets, but ran the same code it too 13s then
first run, then it was <2s consistently


run1: 43265.4296875 43267.2109375
1.78125
run2: 43312.5390625 43313.9765625 1.4375
run3: 43327.2890625 43328.7578125
1.46875
run4: 43357.6484375 43359.0234375 1.375

Sub renane()
Dim t1 As Double
Dim t2 As Double
Dim index As Long

t1 = Timer
For index = 1 To 500
Worksheets(index).Name = "temp" & index & "_" & Int(Rnd() * 10) & 1000
Next
t2 = Timer
Debug.Print "run4:", t1, t2, t2 - t1
End Sub


"rishi doshi" wrote:

Thank God someone else is also facing the same problem :P
heres my issue.
I have 500 sheets in my WB. I rename them as temp1, temp2, temp3 so on...... then again rename them as per index page number.
1st loop runs like this
for i = 14 to sheets.count
sheets(i).name = "temp" & i -12
next
This takes a hell lot of time (2-3 secs per sheet)
i found that out of the following options ...

Sheets(i).Name = "temp " & i - 12 ---- runs slow
Sheets(i).Name = i - 12 + 1000 ---- runs fast
Sheets(i).Name = i - 12 + 2000 ---- runs slow like the 1st option.

its crazy!!
if i use 1500 or any other figure it runs slow. 1000 or 10000 makes the code work fast.

But the 2nd part of my code takes names from an index sheet as follows
sheets(i).Name = Sheets("Index").cells(i,5).Value
Now this i cant get to run fast. it takes 2-3 secs per sheet !!!
(fyi screenupdating and xlCalculations etc are all turned off so thats not the issue)
HELP !!!!!!!!!! :P



MikeZ wrote:

Renaming worksheets taking REALLY long... any ideas?
25-Sep-08

Hi,
I have an application that creates many worksheets in a workbook.
The number of sheets can eventually get quite large (several hundred).

The entire application of creating the sheet, modifying the data etc... goes
very quick. However, I've found that by about the 150th sheet, it can take
up to several seconds just to rename the tab.

I've tried many ideas like closing the workbook every 50 or so new sheets
and reopening to refresh the memory. Memory doesn't seem to be any issue
anymore so I can't figure out what may be causing it.

Here is the basic code relating to the renaming... I'd paste the entire code
but it's way to long to be worth it.

Thanks for the help,
MikeZz

Dim wbFinal As Workbook
'Opens workbook (to clear memory) where tabs need to be renamed.
Set wbFinal = xlAppSum.Workbooks.Open(fileLocAuto & AutoSaveFile)

'Build new sheet name using other variables
sheetName = contractCNum & "."
sheetName = sheetName & contractPNum & "-" & f
'sheetName = sheetName & contractCDat

'Renaming sheet. This one line of code takes seconds which is the real slow
down in my code:
wbFinal.ActiveSheet.Name = sheetName

I've also tried using this line where I don't use "ActiveSheet.Name" and get
same result:
shtCopy.Copy After:=wbFinal.Sheets(wbFinal.Sheets.Count)
Set shtPaste = wbFinal.ActiveSheet
shtPaste.Name = sheetName

EggHeadCafe - Software Developer Portal of Choice
Build a SAPI Text to Wav Converter Library
http://www.eggheadcafe.com/tutorials...xt-to-wav.aspx