Special Copy Method???

[jadox]

Hey guys,

I've got the code above, but the problem is that when I copy the cells it's copying the formulas and all the formatting too. How can I tweak it to only copy the values inside?

Thank you.

Sub cpydat()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, c As Range, fLoc As Range, grp As Range
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
lr = sh1.Cells(Rows.Count, 5).End(xlUp).Row
Set rng = sh1.Range("E2:E" & lr)
For Each c In rng
Set fLoc = sh2.Range("B:B").Find(c.Value, , xlValues)
If Not fLoc Is Nothing Then
Set grp = Union(fLoc.Offset(0, -1), fLoc.Offset(0, 1), fLoc.Offset(0, 2))
grp.Copy c.Offset(0, 3)
End If
Next
End Sub

 

[jadox]

the only fix attempted on your code is to provide the value of a cell, so as you say its not a good solution.

If you need something different you need to explain what you want to happen, and you expected results

What I needed is the find method to copy the values from the cells in the Value column based on the index number (in the index column), but the index numbers in sh2 are not in order so when it found for example the value 234 and the index 3, it copied that value. but after that if above found the value 400 and the index 38 (it starts with a 3) it copied that one and replaced the value in the destination cell...

So I think I found the "cure":

Set fLoc = sh2.Range("B:B").Find(c.Value, , xlValues, lookat:=xlWhole)

...and apparently does the job.

anyway, as always, thanks so much for the help!