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