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