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
.PasteSpecial
? AreTango
andAlpha
(or the rows between them) the result of formulas and you're trying to overwrite the formulas with the actual values? \$\endgroup\$<Qualified>.Range("MyNamedRange").Value = <Qualified>.Range("MyNamedRange").Value
\$\endgroup\$