CODEWITHSUNDEEP
vbaexcel
user249611
user249611

Reputation: 65

copy the formula results from a range to another sheet

This formula resides on one sheet, extracting the values from another column on the same sheet.

sFRM = "=IF(ISNUMBER(SEARCH('IAV',RC[-2])),MID(RC[-2],FIND('IAV',RC[-2])+6,11),'')"

With Sheets("sheet2")
     Set rngOfFindings = .Range(.Range("m2"), .Range("m2").End(xlDown))
     lr = .Cells(Rows.Count, "A").End(xlUp).Row
     .Range("o2:o" & lr).FormulaR1C1 = Replace(sFRM, Chr(39), Chr(34))
End With

I would like to copy the range of values "results of the formula", so far I have not been able to get the correct statement. This is what I have; the result of which copies the formula itself.

With Sheets("sheet2")
     Set RangeToCopy = Sheets("sheet2").Range("o2", ColumnLetterNumber)
End With

CurrentWorkbook.Worksheets("sheet2").Select
'copy this range to sheet1 for more processing
RangeToCopy.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Sheets("sheet1").Range("g1"), Unique:=True

Upvotes: 0

Views: 90

Answers (1)

FreeMan
FreeMan

Reputation: 5687

You need to use .PasteSpecial to paste the values, otherwise, you'll get the formula, as you've discovered.

Upvotes: 1

Related Questions