VBA Copy and Paste Cell Value Only - Not Formula

[wguidry]

I am having problems with the code listed below. The syntax is fine, but it returns a value of #N/A. The code copies a value from a second sheet and pastes it into the current sheet.

The cell being copied derives its value from a VLOOKUP function on a different sheet. I think this may be creating the problem.

Is there a way to copy a cell's numeric value only and not the formula? Or maybe paste the value only (like the Paste Special - Values Only menu item)?
---------------------------------------------
With Worksheets("Daily Calcs")
For i = 2 To 360
If Worksheets("Positions").Cells(i, 20).Value = "RED LINE" Then
Set rng6 = .Range("A3").End(xlDown).Offset(0, 41)
rng6.Copy
ActiveSheet.Paste Destination:=Worksheets("Positions").Range("W2")
End If
Next i
End With

Thanks,
Will

 

[Joe4]

I'm no Pro, but If your Offset and Resize Values are the only values your routine will encounter, I would consolidate those into one, and then copy and .pastespecial .xlValue
ws1.Cells(I, 6).Copy
ws.Cells(9, 4).PasteSpecial xlPasteValues

Not sure if your realize, but the last post in this thread was from over 11 years ago, and the last poster hasn't been on this board in 10 years.

 

[TMan293]

Not sure if your realize, but the last post in this thread was from over 11 years ago, and the last poster hasn't been on this board in 10 years.

OK, but was I right?

 

[TMan293]

OK, but was I right?

and Thanks Joe4

 

[Joe4]

OK, but was I right?

What I am saying is that I doubt that the OP is still looking for assistance on it, since they haven't been active on the board for over 10 years!