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.
--
Cheers,
Ryan


"MikeZz" wrote:

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