Renaming worksheets taking REALLY long... any ideas?

[MikeZz]

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

 

[RyanH]

Because I can not see the entire macro I can only guess a few things.

1.) Cancelling screen updating and calculations speeds up macros
significantly.

Sub test()

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

'your code here

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub

2.) Do you have several Loops in your code. If so you may want to set
conditions before running the loop or while running the loop. This will make
the loop run more efficiently.

3.) Is the macro a recorded macro from the macro recorder? The macro
recorder records a lot of unneccary code that can be cleaned up to make the
code more efficient.

Hope this helps! If so, click "YES" below.

 

[Bob Bridges]

One thing you didn't say, Mike, probably because you haven't tried it yet:
Once you get the workbook up to the point where this is taking so long, what
happens when you try to rename a sheet manually?

If it takes as long doing it manually, then the problem isn't in your code.
Maybe Excel just does a lot of cross checks before renaming a sheet, and it's
going to take this long no matter what you do to your code.

Or here's a thought: Do all these sheets refer to each other a lot? Do
they have a lot of formulae referring to other sheets? If so, naming a new
sheet WOULD involve a lot of cross-checking. If you can, try creating all
the sheets first, and not actually populating any cells with formulae that
refer to other sheets until afterward; maybe that'll speed it up. Again, if
you can't do that, you may be stuck with the delay, not because of any flaw
in your code but simply because Excel has so much it has to do in the process
of renaming a sheet when there are lots of sheets and lots of formulae that
use those sheet names.

(Several hundred sheets, eh? I once made a workbook with about 60 sheets,
and I thought that was some kind of record; I NEVER imagined I could need
that many! You've got me 'way beat.)

 

[MikeZz]

Hi Ryan,
I ran a test (code is below) and got the following results.
Screen Updates & Calc Off have minor update but not signficant.

The "macro" is hardly a macro... it's a complex piece of VBA programitry.
Probably close to 50 different routines and functions totalling a couple
thousand lines of code. There is no "record" a macro in it.

In my initial post, I didn't mention that each sheet has about 30 named
ranges. I use them as reference points to paste various data where it needs
to go. I found that if I remove all the named ranges, it dramatically
improves the renaming time.

To be honest, I'm not sure why named ranges make a difference because there
are no references between sheets at the time I do the re-naming. In
addition, it doesn't take much time at all to make a copy of the worksheet
which has all the named ranges as well. If I can't find another work around,
I'll have to probably add a couple more routines to work around the naming
issue since it's an important part of the code.

Thanks for the feedback,
MikeZz



Col1 = Screen Update Off
Col2 = Screen Update On
Col3 = XLCalc On
Col4 = All On, No Names
Col5 = All Off, No Names
Col6 = All Off 3 Names
Sheet Count Rename Time
203 0.105 0.105 0.134 0.04 0.007 0.05
204 0.198 0.198 0.23 0.042 0.01 0.059
205 0.296 0.298 0.345 0.048 0.013 0.073
206 0.391 0.397 0.43 0.048 0.016 0.084
207 0.491 0.494 0.546 0.05 0.018 0.096
208 0.598 0.596 0.641 0.052 0.02 0.107
209 0.696 0.694 0.734 0.056 0.022 0.119
210 0.798 0.8 0.835 0.057 0.026 0.129
211 0.897 0.902 0.944 0.06 0.028 0.142
212 0.21 0.209 0.237 0.044 0.011 0.065
213 0.306 0.305 0.342 0.046 0.013 0.072
214 0.408 0.408 0.45 0.049 0.015 0.086
215 0.512 0.511 0.546 0.052 0.017 0.098
216 0.613 0.618 0.654 0.053 0.02 0.109
217 0.724 0.719 0.773 0.055 0.023 0.119
218 0.831 0.851 0.868 0.059 0.025 0.127
219 0.925 0.96 0.983 0.061 0.028 0.136
220 1.035 1.095 1.101 0.064 0.03 0.147
221 1.148 1.224 1.189 0.065 0.032 0.155
222 1.358 1.417 1.423 0.072 0.038 0.187
223 1.472 1.481 1.512 0.075 0.041 0.189
224 1.592 1.583 1.642 0.076 0.042 0.202
225 1.694 1.703 1.744 0.078 0.046 0.21
226 1.809 1.812 1.856 0.079 0.049 0.225
227 1.916 1.929 1.978 0.087 0.05 0.244



Declare Function timeGetTime Lib "winmm.dll" () As Long
Sub test()

Dim t1, t2, t3, t4
Dim tCopy, tRename
Dim sCount
Dim i
Dim arrTemp(0 To 100, 1 To 3)

Set sht = ActiveSheet
sCount = ActiveWorkbook.Sheets.Count

arrTemp(i, 1) = "Sheet Count"
arrTemp(i, 2) = "Copy Time"
arrTemp(i, 3) = "Rename Time"

Application.DisplayAlerts = False
'Application.ScreenUpdating = False
'Application.Calculation = xlCalculationManual
For i = 1 To 25
t1 = timeGetTime
sht.Copy After:=ActiveWorkbook.Sheets(2)
t2 = timeGetTime
ActiveSheet.Name = "test" & i
t3 = timeGetTime
tCopy = t2 - t1
tRename = t3 - t2
arrTemp(i, 1) = sCount + i
arrTemp(i, 2) = tCopy / 1000
arrTemp(i, 3) = tRename / 1000
' GoTo endit
Next i
'Application.Calculation = xlCalculationAutomatic
'Application.ScreenUpdating = True
Application.DisplayAlerts = True

endit:
Sheets("Data").Range("a1:c100") = arrTemp

End Sub

 

[MikeZz]

Hi Bob,
I've tried to manually change a sheet name and it's relatively instant.

Each sheet does have:
--Some formulas (all self contained to the sheet)
--Some conditional formatting (all self contained to the sheet)
--about 30 named ranges per sheet (used as reference points to paste data)
--hyperlinks to and from a Summary Tab

I ran a test (code is below) and got the following results.

I found that if I remove all the named ranges, it dramatically improves the
renaming time.

To be honest, I'm not sure why named ranges make a difference because there
are no references between sheets at the time I do the re-naming. In
addition, it doesn't take much time at all to make a copy of the worksheet
which has all the named ranges as well. If I can't find another work around,
I'll have to probably add a couple more routines to work around the naming
issue since it's an important part of the code.

Thanks for the feedback,
MikeZz

Col1 = Screen Update Off
Col2 = Screen Update On
Col3 = XLCalc On
Col4 = All On, No Names
Col5 = All Off, No Names
Col6 = All Off 3 Names

Data for running attached loop copying and renaming 25 sheets in a row.
Sheet Count Rename Time
203 0.105 0.105 0.134 0.04 0.007 0.05
204 0.198 0.198 0.23 0.042 0.01 0.059
205 0.296 0.298 0.345 0.048 0.013 0.073
206 0.391 0.397 0.43 0.048 0.016 0.084
207 0.491 0.494 0.546 0.05 0.018 0.096
208 0.598 0.596 0.641 0.052 0.02 0.107
209 0.696 0.694 0.734 0.056 0.022 0.119
210 0.798 0.8 0.835 0.057 0.026 0.129
211 0.897 0.902 0.944 0.06 0.028 0.142
212 0.21 0.209 0.237 0.044 0.011 0.065
213 0.306 0.305 0.342 0.046 0.013 0.072
214 0.408 0.408 0.45 0.049 0.015 0.086
215 0.512 0.511 0.546 0.052 0.017 0.098
216 0.613 0.618 0.654 0.053 0.02 0.109
217 0.724 0.719 0.773 0.055 0.023 0.119
218 0.831 0.851 0.868 0.059 0.025 0.127
219 0.925 0.96 0.983 0.061 0.028 0.136
220 1.035 1.095 1.101 0.064 0.03 0.147
221 1.148 1.224 1.189 0.065 0.032 0.155
222 1.358 1.417 1.423 0.072 0.038 0.187
223 1.472 1.481 1.512 0.075 0.041 0.189
224 1.592 1.583 1.642 0.076 0.042 0.202
225 1.694 1.703 1.744 0.078 0.046 0.21
226 1.809 1.812 1.856 0.079 0.049 0.225
227 1.916 1.929 1.978 0.087 0.05 0.244



Declare Function timeGetTime Lib "winmm.dll" () As Long
Sub test()

Dim t1, t2, t3, t4
Dim tCopy, tRename
Dim sCount
Dim i
Dim arrTemp(0 To 100, 1 To 3)

Set sht = ActiveSheet
sCount = ActiveWorkbook.Sheets.Count

arrTemp(i, 1) = "Sheet Count"
arrTemp(i, 2) = "Copy Time"
arrTemp(i, 3) = "Rename Time"

Application.DisplayAlerts = False
'Application.ScreenUpdating = False
'Application.Calculation = xlCalculationManual
For i = 1 To 25
t1 = timeGetTime
sht.Copy After:=ActiveWorkbook.Sheets(2)
t2 = timeGetTime
ActiveSheet.Name = "test" & i
t3 = timeGetTime
tCopy = t2 - t1
tRename = t3 - t2
arrTemp(i, 1) = sCount + i
arrTemp(i, 2) = tCopy / 1000
arrTemp(i, 3) = tRename / 1000
' GoTo endit
Next i
'Application.Calculation = xlCalculationAutomatic
'Application.ScreenUpdating = True
Application.DisplayAlerts = True

endit:
Sheets("Data").Range("a1:c100") = arrTemp

End Sub

 

[MikeZz]

Just a side to my other 2 responses....

I get the same rapid progression ( of time to rename ) even when the file
has substantially fewer sheets (and named ranges).

Col3 & 4 Screen Updating ON
Col5 & 6 Screen Updating Off
Sheet Count
CopyTime RenameTime CopyTime RenameTime
7 0.206 0.01 0.311 0.003
8 0.174 0.011 0.142 0.005
9 0.167 0.014 0.144 0.008
10 0.265 0.023 0.145 0.014
11 0.176 0.024 0.145 0.019
12 0.211 0.03 0.141 0.027
13 0.172 0.043 0.144 0.033
14 0.192 0.047 0.184 0.044
15 0.219 0.059 0.147 0.053
16 0.203 0.019 0.181 0.014
17 0.183 0.026 0.157 0.02
18 0.207 0.036 0.182 0.03
19 0.197 0.046 0.202 0.04
20 0.242 0.056 0.169 0.053
21 0.202 0.07 0.189 0.07
22 0.218 0.084 0.172 0.077
23 0.227 0.097 0.18 0.09
24 0.201 0.112 0.179 0.107
25 0.228 0.13 0.21 0.126
26 0.212 0.161 0.186 0.155
27 0.216 0.185 0.184 0.175
28 0.215 0.208 0.229 0.198
29 0.215 0.22 0.195 0.216
30 0.222 0.247 0.19 0.242
31 0.219 0.269 0.194 0.265

 

[rishi doshi]

Thank God someone else is also facing the same problem
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 !!!!!!!!!!



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/tutorial...4b-b72a56cc906f/build-a-sapi-text-to-wav.aspx

 

[Patrick Molloy]

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

 

[Charlie]

Do any of these sheets have formulas referencing cells on the other sheets?
e.g

(Sheet2 contains) ='Sheet1'!A1
(Sheet3 contains) ='Sheet2'!A1
(Sheet4 contains) ='Sheet3'!A1 ... and so on ...

....whereby renaming the sheet causes all of the formulas on the other sheets
to also be renamed (not recalculated). I was not able to get the "slow
renaming" problem to appear. I'm just thinking out loud.

 

[Rishi]

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.. 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

 

[Rishi]

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...

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.. 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

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