Removing formulas from a sheet but keeping the results

fugeg70

Board Regular
Joined
Mar 26, 2003
Messages
114
Hello all,

I have been creating a very large spreadsheet (heavily formated, heavily formulated) over the past financial year.

I now want to send the results out to people. The ideal thing would be if I could just remove all the formulas from the sheet and keep the results.

I tried copy and paste, etc but the formating wasn't really working. It would be so easy for me if I could remove the formulas....(many thanks!!!!)

PS Is formulated a real word or did I just invent a new one?

Cheers from fugeg70
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi firefytr,

Many thanks.....

Yeah I tried first to copy the format then paste special and then to copy the values and paste special them on top. However, it mucked up all my printing and borders, etc. It is an option and may be what I need to do but there is a massive amount of work required to go though and get it all looking exactly the way it does with the formulas in it.....nightmare!

G.
 
Upvote 0
Following on Zacks suggestion, this may speed things up for you a bit. It will Copy-->Paste Special-->Values in each sheet in your workbook.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CopyValues()
    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
        ws.Activate
            <SPAN style="color:#00007F">With</SPAN> ActiveSheet.Cells
                .Copy
                .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                <SPAN style="color:#00007F">False</SPAN>, Transpose:=<SPAN style="color:#00007F">False</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
            <SPAN style="color:#00007F">With</SPAN> Application
                .CutCopyMode = <SPAN style="color:#00007F">False</SPAN>
                .Goto Reference:=[<SPAN style="color:#007F00">'Sheet1'!A1]</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> ws
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top