Optimizing VBA Select Code
To start with:  You are currently not using the wb Workbook object after setting it.  Is it needed in other code, or can it be removed?


If you are just trying to 'flatten' the range to get rid of any formula, you can just use &lt;Range&gt;.Value = &lt;Range&gt;.Value, instead of Copy / PasteSpecial

Range("S" &amp; FoundCell1.Row + 1 &amp; ":S" &amp; FoundCell2.Row - 1).Select
'Selection.Copy
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
     :=False, Transpose:=False
Selection.Value = Selection.Value


You can also use a With Statment to replace the requirement for a Select or Selection.

'Range("S" &amp; FoundCell1.Row + 1 &amp; ":S" &amp; FoundCell2.Row - 1).Select
With w.Range("S" &amp; FoundCell1.Row + 1 &amp; ":S" &amp; FoundCell2.Row - 1)
    .Value = .Value
End With
'Selection.Copy
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
     :=False, Transpose:=False


And, this is personal preference, but you could use .Offset, .EntireRow and .Column to define the range instead of building a text string address:

With ws.Range(FoundCell1.Offset(1, 0), FoundCell2.Offset(-1, 0)).EntireRow.Columns("S")
    .Value = .Value
End With


(This only requires "Column S" to be defined once, in case you need to change it later - the other option would be to have it in a Const at the start of the Sub)

As yet-another-alternative, you could use WorksheetFunction.Match to retrieve the Rows directly (TangoRow = WorksheetFunction.Match(WHAT_TO_FIND1, ws.Columns("A"), 0) would give the same result as FoundCell1.Row)