Optimizing VBA Select Code

Question

I'm trying to reword the code below to avoid using the Select and Selection terms. I tried to Range("S"...).Copy instead of .Select but that didn't work for some reason. Does anyone know a way I can reword this to avoid Select and Selection statements and why my change .Select to .Copy wouldn't have worked?

The code below looks in Column A for the identifiers Tango and Alpha. Then it uses those identifiers to figure out the range between rows to select in Column S and copies and pastes them as values. I can confirm the code does run as is.

Sub copy_paste()
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim FoundCell1 As Range
        Dim FoundCell2 As Range
        Set wb = ActiveWorkbook
        Set ws = ActiveSheet

        Const WHAT_TO_FIND1 As String = "Tango"
        Set FoundCell1 = ws.Range("A:A").Find(What:=WHAT_TO_FIND1)

        Const WHAT_TO_FIND2 As String = "Alpha"
        Set FoundCell2 = ws.Range("A:A").Find(What:=WHAT_TO_FIND2)

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

End sub

Answer 1

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 <Range>.Value = <Range>.Value, instead of Copy / PasteSpecial

Range("S" & FoundCell1.Row + 1 & ":S" & 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" & FoundCell1.Row + 1 & ":S" & FoundCell2.Row - 1).Select
With w.Range("S" & FoundCell1.Row + 1 & ":S" & 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)