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

 

[mole999]

untested

grp.Copy c.Offset(0, 3).Value

 

[jadox]

Nope
Runtime Error 1004

 

[shg]

Maybe ...

            grp.Copy
            c.Offset(, 3).PasteSpecial Paste:=xlPasteValues

 

[jadox]

c.Offset(,3).PasteSpecial Paste:=xlPasteValues

returns

Compile Error: Expected end of statement.

it's driving me crazy

 

[shg]

No compile error for me.

 

[jadox]

Thanks Shg, solved it.

But now I'm having another issue: The find method that I use in the same code is not not working properly: with cells that contain text is ok but with those that contain numbers it doesn't make the difference between 1 and 17 or 2 and 29, (it's not case sensitive, I think). Can you help me with that too?

thanks!

 

[mole999]

try

lr = sh1.Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row

 

[jadox]

it's not working because the cell values are not in order. I could have something like this:

index	Name 1	Name2	Name3	Name4	Value
1
2
3
4
5
6
10
11
12
13
21
22
23
24

<tbody>
</tbody>

sh1

index	Name1	Name2	Name3	Name4	Value
2					324
15					4234
4					234
17					234
25					23
68					56
11					567
43					456
6					456
8					67
19					34
21					45
56					56
5					34

<tbody>
</tbody>

sh2


I need to copy the values from sh2 from the column Value to the Column Value in sh1 but the index numbers in sh2 are scrambled so the code I have is not differentiating between 1 and 11 or 2 and 22 it copies the firs value whose index "matches" first, so going through the rows one by one is not a good solution.

Can anyone help please?

 

[mole999]

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