Worksheets in Excel - Releasing Memory

[jeffcravener]

I have done some research and found that the number of worksheets allowed to be created in a workbook is based on the systems memory.

I have an automated workbook that can, and will, create 200+ worksheets.

Now, once i get to a certain amount (120 the last time) it just stopped...ven the copy function was looping, no pages were created.

BUT, when i closed out the workbook, and then re-opened it, and started again, it created the remaining worksheets. So, I am guessing it was 'holding' on to memory as it was going along.

IS there a way to release any memory it is using when it creates a worksheet, so i don't have to have to close it out, each time, and re-open it?

FYI: I already have DoEvents in all of my LOOPs.

I also have this function:

Public Function Holding(Sec As Integer)
    Dim i As Long
    
    If Sec = 0 Then Exit Function
    
    For i = 0 To Sec
        Call Sleep(1000)
        DoEvents
    Next
    
End Function

And i use it after each 'grouping' of sheets to be created, wth 5 seconds as the value passed.

[Ecniv]

Just a thought, would saving the worksheet solve the problem?

As another thought, why 200+ sheets in a spreadsheet? Time to move to a database????


Vince