How to save an entire workbook, but only Values?

[Alejo1970]

I have a very large XL workbook, with 20+ worksheets and many complex formulas, which has grown to over 35 MB. I want to distribute it to other people, but only the values, to keep the file light.

Is there any way to save entire the file with just the values? (without having to go worksheet by worksheet and doing Copy -> Paste Values)?

A solution not involving macros is preferred. Thanks!!

-Alejandro

 

[big_mac]

FYI before you try anything BACKUP!! BACKUP!! and oh yea BACKUP!!

I just wanted to remind you before you try a macro, that looses all those valuable formulas and replaces them with VALUEs.

 

[Alejo1970]

Of course, I'll keep a separate copy with the full formulas, etc. Thanks for the advice, though.

Now, any idea of how to go about actually saving the entire file as just values?

Any help is appreciated. Thanks.

 

[big_mac]

Here is what I used when I had to do the same thing every month, I hope it helps. I just copied it into a module use F8 to visually make sure it is working for your application. And if you can tell sometimes I failed to backup, Thank God for IT & Network Admin.
Sub thevalues()
CHECK = MsgBox("Make a copy before this is ran!", vbOKCancel, "Just Checking")
If CHECK = 1 Then
For Each ws In Worksheets
other = ws.Name
Worksheets(other).Activate
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=True, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Next ws
Else: End If
End Sub

 

[Alejo1970]

Thanks for the solution! It uses macros, though. Does anyone have another solution without macros? (I'm VBA-challenged.)

 

[unmarkedhelicopter]

You could print it to a pdf and distribute that instead, pdf's are quite economical on file size and you can set different levels of security to stop people from messing with it (according to your needs).

 

[Jubjab]

You could try to just select all sheets (select the leftmost one, then shift-click on the last one), then select all cells (the small box left of column A and above row 1), then copy & paste as values.

 

[unmarkedhelicopter]

You could try to just select all sheets (select the leftmost one, then shift-click on the last one), then select all cells (the small box left of column A and above row 1), then copy & paste as values.

No, you really don't want to do that. ANY complexity in the WB just leads to massive problems.

Having said that if your WB is simple enough then go for it (but at 35MB, I doubt it !)

 

[big_mac]

I was messing around with all the ways to select, copy and paste.
I got nothing.
I could group all sheet & select all like Jubjab suggested (I liked that), but I couldn't copy all selections & paste value. It is probably why I made a macro for it. Of course after I saved the values I removed the macro before distribution.

 

[Alejo1970]

IT WORKED!!

Thanks Jubjab for your solution!! It worked just fine with my (now 43MB and growing) workbook.

Simple, elegant and effective. Beautiful.

The only thing you need to make sure is that the target workbook has enough empty worksheets on it, so that all tabs fit in. Other than that, it just works!

Thanks again.