Too many sheets to save.

[rdrum]

My workbook has so many sheets that it takes a long time to open and save. What is the best method to deal with this, I have heard of archiving them, but haven`t found any info on it, Thanks.

[Jack in the UK]

Before I advise you on things that might help, can i request

Size or WkBk and number of sheets, im sure someone will just post ways to reduce sheet and wkbk size, but if its THAT big, we need to look a little deeper.

How long to open and close… any other issues I need it know..

[rdrum]

Thanks, Jack
My work book isn`t that big really,I have about 50 sheets in it now and each sheet contains 3 pages. It is my daily production reports, and I make a new sheet every day. Thanks. It takes a little over a minute to open and the same to save.

[ This Message was edited by: rdrum on 2002-05-25 12:41 ]

[Jack in the UK]

OK sounds good, the open time seems to be related to the amount of cross referencing from sheet a to sheet c d and so on this will made excel slow.

Suggestions:

Can you make new sheets of a older data???? You need to kill active formulas, copy paste special Values else you get ref errors all over the place.

Also if not can you kill some sheets IE the older ones so the formatting and formulas do not need to be used.

I suggest if you try this to save a new version called JACK..whatever and play on a copy NOT The original just in case

Also look into complex formula that are ROW or COLUMN long you may fine these are out side the required space needed, again this could mean you use say 1000 rows and have 65000 or formula, all will slow the sheet open and close.

Some thing to look into. Let me know if you need more ideas.

A lot depends on windows version and PC processing speed, but look at the sheets first

[sen_edp]

hello there,
If you have Outlook
installed, whether you use it or not, there is a feature in there which
keeps a record of what you do in office. This is the default setting
for Outlook. Open Outlook, go into Tools->Options and select the
Journal Tab. If there is a check in the boxes next to the office
product, uncheck them. Then click OK or apply or whatever and back out
of Outlook.
Try to save the workbook again and watch for difference in time.

Hope that help

[rdrum]

Thanks, people,I`m still kind of new, so will do some experimenting with this, it sounds like my problem though, thanks a lot.

[Joe Was]

As you are new to this. An easy no code way, is to open a new blank Workbook and name it ARKyourFileName-Date.xls
Then add tab-sheets for a group of your master sheets and copy them from your original Workbook to your ARK workbook.
Then delete the ARK'ed sheets from the master Workbook.

You could do this with code. Search my name "Joe Was" open the file "Date Formula" for the code to build a date saved Workbook. The idea is you build a psudo-template Workbook (your journal for one period) and every period that you open it on, a copy gets saved as your file name, with the date amended to the end of the file name. This way if you put your master in it's own folder (path-directory) then every period gets saved, as a new file, with a name-date format to the file name, in the same directory as the master. The master is not saved and it is clean for the next period. Only the date-amended files have data in them! Hope this helps. JSW

[ This Message was edited by: Joe Was on 2002-05-25 23:30 ]

[ This Message was edited by: Joe Was on 2002-05-25 23:32 ]

[Nimrod]

another thing that help me in the time it took to open and close books is to set ... tools ... options... calculation ... to "manual" and uncheck "caluculate before save"

[Chris Davison]

also, at work, I save everything to my C drive rather than the network then just zip the respective folders and copy them to the network everynight

I'd estimate it saves a good working hour each week in save/load times

[Jack in the UK]

Good points Chris, back ups are apain as we dont like them very much boring i guess. this trick is neet, im working on a way to allow back ups in XLS ZIP and to local and native network domains for the user, and also version saves. These will come in toime.

The trick of loacl opn save against network is true, not sure if over 1000mb thissi true thou, but that will depend on cat cables also.