Remove formula except specific range

Question

I found a script that removes formulas from all sheets in my file, and leaves values and formatting. I would like to add some code that allows me to keep some formula in certain tabs + ranges (I might want to keep formulas in multiple tabs and ranges so please build that into the solution or show how I could add another range).

Sub Saveasvalue()
    Dim wsh As Worksheet
    For Each wsh In ThisWorkbook.Worksheets
        wsh.Cells.Copy
        wsh.Cells.PasteSpecial xlPasteValues
    Next
    Application.CutCopyMode = False
End Sub

I don't understand what you want to do with the formulas. Can you please add an example to your question. — Rich Michaels, Jun 20 '19 at 11:05
Hello, I have an excel file with numerous tabs, lots of formulas within, when sending out the view only file to co-workers, I want to remove all formulas, only values + formatting to remain. The script above does that, though now I need to add an exception, for example leave formulas in tab 1, cell range A10:A20 etc? Does that help explain the issue? — H Singh, Jun 20 '19 at 12:23
Have you tried to write some additional code to perform this selective copy function that you want to do? Who wrote the code you are showing us? Maybe they can help. You need to add some additional conditional checking. e.g. If wsh.Name = "Sheet 1" and wsh.Cells.Range ... — Rich Michaels, Jun 20 '19 at 21:06
Hello, so instead of the line 'For each wsh....' swap that for the conditional line is that right? Also how can I add a line for multiple sheets and ranges.. — H Singh, Jun 21 '19 at 08:41
Use a named range "exclude" on each sheet and then [subtract](https://stackoverflow.com/a/21589364/1365754) that range from wsh.cells (prolly better to use the [used range](https://stackoverflow.com/questions/7423022/getting-the-actual-usedrange) only as this could be slow, not sure). Or copy the excluded range to a temporary sheet and then copy it back after saving the values. — Hubisan, Jul 01 '19 at 07:17
Sorry I am new to scripts, could you amend my original script to show how it could work, for example lets say I have 2 x named ranges, Range1/Range2, and I want to exclude these named ranges from the remove formula script, can you edit my code to show me an example of how it would look? Lets say the Named ranges happen to be on Sheet1, and Sheet 2 does that make a difference to the code? — H Singh, Jul 05 '19 at 10:47
Instead of looking to exclude certain ranges, you could simply convert explicitly the ranges from Formulas to Values using multiple lines such as `Worksheets("Sheet1").Range("A:A").Value = Worksheets("Sheet1").Range("A:A").Value` which will convert Column A of Sheet1 to values, do this for as many ranges as you want to convert.... — Xabier, Jul 23 '19 at 09:23

score 0 · Answer 1 · answered Jul 23 '19 at 09:45

suppose you want to escape the range B2:B8

Try Please this macro

Option Explicit

Sub keep_formula_In_Spesific_range()
 Dim Cel As Range
  For Each Cel In Sheets("sheet1").UsedRange. _
   SpecialCells(xlCellTypeFormulas, 23)
    If Intersect(Cel, Range("B2:B8")) Is Nothing Then
      If Cel.HasFormula Then
       Cel = Cel.Value
      End If
    End If
 Next Cel
End Sub

enter image description here

Remove formula except specific range

1 Answers1