Splitting workbooks, memory problem

[Yarroll]

Hi,
What would be the simplest way to split a workbook (save individually each
worksheet) into separate workbooks?
I tried using the following macro.


Dim x As Worksheet
For Each x In ActiveWorkbook.Sheets
x.Activate
x.Copy
ActiveWorkbook.Close True, "C:\Windows\Pulpit\Data\" & x.Name
Next x
End Sub
--

The workbook to be split was some 25 MB, when running it Excel says "Out of
disk space" (there's some 400 MB free on C drive, also the 25 MB is on C
drive).

My second question is, is there a way to release memory after I use Excel.
My Excel (2000) acts very strange with frequent "Out of memory" messages and
won't shut down except by Ctrl Alt Del. I can't even open VBEditor
sometimes, which is very annoying as I have to open it quite often. The
problem goes away after a reboot, then I have a chance to do what's
necessary with VBE, run a few computation-intensive macros and that's it, no
memory, no VBE anymore until the next reboot. What's up? I've been using
Excel for some years, never saw something like this.
My Personal.xls is some 1.5 MB and much less what it used to be, for I
deleted macros I didn't use that often. I compiled Pesonal.xls. I keep
deleting Temp files and Office Recent files after reboot. Clipboard is
empty, etc. I almost completely gave up using personalized toolbars. It
looks as if Excel is eating fast all available memory even though I shut it
down between tasks, and only reboot helps (for a while). I normally have
some other intensive processes running (non-Office) but no more intensive
than before. I think it has something to do with VBE, if I just run macros
and use Excel without opening VBE, all seems to be fine (well, lasts longer
after reboot), but sooner or later I have to do something in VBE and there
we go again.
Please advise :-((

Thanks. Best regards

Yarroll

 

[Dave Peterson]

Just guesses:

Your code looks fine to me. But maybe windows is having trouble with your
virtual memory and is eating up more than you realize. Is there anyway you
could try the same stuff on a drive with more space?

I use xl2002 and win98 at home. I can get some "cannot complete xxx operation"
when I've got too many things open at once. Maybe a windows upgrade would
help. I use xl2k and winNT at work. It seems like winnt is much better at
memory management than win98.

And I'd take a look at Charles Williams' site for more info:
http://www.decisionmodels.com

And David McRitchie has some notes that might help at:
http://www.mvps.org/dmcritchie/excel/slowresp.htm

And I've read posts that remark that macros slow down with the VBE open (if I
recall correctly).

But it sure sounds like you're doing all you can do to help.

Good luck,

 

[Yarroll]

Thanks Dave.

Best regards,
--
Yarroll
***

Just guesses:

Your code looks fine to me. But maybe windows is having trouble with your
virtual memory and is eating up more than you realize. Is there anyway you
could try the same stuff on a drive with more space?

I use xl2002 and win98 at home. I can get some "cannot complete xxx operation"
when I've got too many things open at once. Maybe a windows upgrade would
help. I use xl2k and winNT at work. It seems like winnt is much better at
memory management than win98.

And I'd take a look at Charles Williams' site for more info:
http://www.decisionmodels.com

And David McRitchie has some notes that might help at:
http://www.mvps.org/dmcritchie/excel/slowresp.htm

And I've read posts that remark that macros slow down with the VBE open (if I
recall correctly).

But it sure sounds like you're doing all you can do to help.

Good luck,