On save, save values and not formulas?

[KrazyKevin]

I have a large excel spreadsheet that looks up data on another server with an addin. I have many formulas in my spreadsheet. My problem is at the beginning of the month, the spread sheet is empty so it loads pretty quick. All the data that its looking up is empty. But, towards the end of the month, when there is over 300 entries, the load time is very slow. All the data in this ss is based on a time entered in column X Row 1, where X is A-IO. So when the ss opens up it looks at A1, and starts retrieving data, and it does this all the way to column IO. Whats the best way to fix this where once data is gathered, it will only store a value? The problem I'm running into is, I need formulas in the cells until we put in a date/time is Col X, Row 1. Once that time is entered, the ss goes and gathers data from a server, and performs my calculations. Once that is done, I don't need the formulas. If I save the ss, the next time it is opened, there is no actual values saved, its all formulas. It then has to go out and regather and recalculate. Is there a way to do this when we save the ss? So at the end of the day, when we hit save, the spread sheet will save only the value in the cell instead of the formula? Thanks in advance.

[cycling-rod]

On a copy of your spreadsheet (in case it doesn't work) try setting protection over the whole sheet. Rename the tab to "Sheet1 old" or use a date. Copy the whole spreadsheet and paste it into sheet 2. The copied and pasted formulas and data now on Sheet2 will still work and can be copied, printed, etc. No changes can be made to the data or formulas on "Sheet1 old" as it is now protected. "Sheet1 old" could be used as a back-up in case of accidents with Sheet2. Insert another sheet when required, and carry on.

[KrazyKevin]

I understand what you are saying. But, isn't everytime I open up the spreadsheet its going to recalculate everything? I'm just asking here. I'm not able to work on it right at this momement. Maybe I'm just not understanding.

[cycling-rod]

KrazyKevin,

What a silly me! When you come to Paste into sheet2 (see previously posted answer) use Paste Special (rather than just Paste) and tick Value. Then protect the sheet so no more changes can be made to the data. The data on the sheet will no longer be affected by formulas either - as there won't be any!

[cycling-rod]

Once a copy of the spreadsheet is taken and Pasted Special with Value ticked into another blank spreadsheet, there will not be any formulas on the newly pasted sheet - as static as figures written on a piece of paper. But the original spreadsheet can now be emptied of data, leaving the formulas in place, and ready to receive the next lot new data - if you follow?

[KrazyKevin]

I'm with you now. I should have picked up on that. Now, see if you can think of a way to make it easier. I follow you fine, but my co workers probably won't. Or, I don't want them to have that much control. I wouldn't mind doing it myself, but we work shift work, and i'm not here every other 4 days. Would there be any way to make a macro or something similar so when they save it or shut it down, that it would copy only the cells with inputed or calculated data. I wouldn't even mind if I had to paste special on top of the original data. Once we enter our data, we have no need to go back and change anything. This way, there would only be one sheet for them to look at. So basically, if there is data in Col' A,B, and C. But D,E,F..... or clear, how could I make a macro that would copy A,B,and C column, and paste special on top of itself when someone would close the spread sheet. BUt not touching the other columns. Is this asking to much? Thanks for the previous help.

[cycling-rod]

I would love to help! But I've not yet reached the skill to delve into programming, unless I can copy and use other's code. Good luck.