REMOVING FORMULAS

[FARAZ QURESHI]

I have a regular daily exercise to submit a report without any formula and
therefore have to copy all the data and paste special values of the same.
Any shortcut to break such links/formulas in a single snap for:
1. The active sheet only? and
2. The whole workbook?

 

[JLatham]

You could put these macros in your Personal.xls workbook (or any workbook
that you could open along with the workbook to be altered) and set up
shortcuts to them:

Sub FormulaToValue1Sheet()
ActiveSheet.UsedRange.Copy
ActiveSheet.UsedRange.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

Sub FormulaToValueAllSheets()
Dim anySheet As Worksheet
For Each anySheet In ActiveWorkbook.Worksheets
anySheet.UsedRange.Copy
anySheet.UsedRange.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Next
End Sub

 

[Teddy]

Hi there,
a quick way of breaking the links is by doing the following and the values
only are left:
in 2003 Click Edit, Links then Break Links.
or in 2007 click Data, Connections, Edit Links, Break Link

Hope this helps. The only way I know to replace formulas however is what
you are doing but somebody else here may have a better way.

Rgds,
Teddy