Copy of workbook without formulas

I can only think of a VBA soloution for this.

This will take your current workbook, convert all the Formulas to their values and then save it as another Workbook with a "No formulas" suffix on the end so the original Workbook will still exist.

Please Login or Register  to view this content.

Thanks for your reply!

For some reason, the document freezes when I run the code though.

Originally Posted by FDibbins

You could cop/paste values before you save?

The problem is that the formulas need to be active, to keep the document updated with the latest information.

Your original question was How can I make a copy of a whole workbook, saving everything except the formulas?
.
So copy pasting everything then save as would do the trick.
No?

My code works on a test file that I created of 3 Worksheets with some basic formulas thrown in.

It basically takes the current Workbook, copies and pastes all the sheets as values and then saves it as a copy of the Original with a suffix so you can easily identify it and distrubute it without any links to other workbooks etc.

The original workbook you run the code in will still have the Formulas so it can be updated.

Not sure why it would freeze anything up though, the only thing I can think of is that you've run it in a Macro enabled Workbook (.xlsm) when I'm trying to save it as a normal (.xlsx) file, though I'd expect an error and not a freeze.

Anyways, this should account for that;

Please Login or Register  to view this content.

If that still doesn't work and if my soloution seems like something you could work with then post a sample of your Workbook as per the instructions on the top Yellow Bar above and we can go from there.

The formulas should be in the original file?

The code saves the Workbook as a copy leaving the original intact?

I'm confused, that's what my code is doing.

I've attached a sample workbook.

Download it to a folder on your PC.

Run the Voluntas Macro.

You'll then have two workbooks in that folder - "Voluntas.xlsm" and "Voluntas No Formulas.xlsx"

"Voluntas" will have formulas, the other won't.

Thanks for your help guys!

I tried the macro and the formulas convert to text for both "Voluntas.xlsm" and "Voluntas No Formulas.xlsx".

Then, that's odd. I've tried it in both Excel 2013 and 2021 and it works.

I don't understand how "Voluntas.xlsm" is getting converted to text, as soon as it's 'saved as' "Voluntas No Formulas.xlsx" then "Voluntas.xlsm" should be forgotten entirely, there is nothing there that should save any changes to that Workbook

It shouldn't even be left open when the code has finished to give an option to manually save changes.

Sorry, but I'm at a loss now and I've no way of testing it on 365 to see if I can find anything that way.

Hopefully someone else can come along and see what the differences could be.

Thanks for your help anyway! This forum is amazing!