Am Thu, 3 Oct 2013 02:01:14 -0700 (PDT) schrieb Howard:







I like the non-copy paste you suggest. I'm sure its possible to include a Transpose or Values.







to get only the values try:







Application.ScreenUpdating = False



For Each c In ws1Part_Num



Set rngFndPrd = ws2From_Item.Find(c, LookIn:=xlValues, _



lookat:=xlWhole)



If Not rngFndPrd Is Nothing Then



With Sheets("Sheet1")



.Range("F100").End(xlUp).Offset(1, 0).Value _



= rngFndPrd.Offset(0, 5).Value



End With



End If



Next



Application.ScreenUpdating = True







to get the values and transpose the range try:







Application.ScreenUpdating = False



For Each c In ws1Part_Num



Set rngFndPrd = ws2From_Item.Find(c, LookIn:=xlValues, _



lookat:=xlWhole)



If Not rngFndPrd Is Nothing Then



If rngBig Is Nothing Then



Set rngBig = rngFndPrd.Offset(0, 5)



Else



Set rngBig = Union(rngBig, rngFndPrd.Offset(0, 5))



End If



End If



Next



rngBig.Copy



Sheets("Sheet1").Range("F100").End(xlUp).Offset(1, 0) _



.PasteSpecial xlPasteValues, Transpose:=True



Application.ScreenUpdating = True











Regards



Claus B.


Okay, got it.

Thanks a ton, Claus.



Regards,

Howard

Just noticed as I was pasting in my worksheet, with the transpose code you have introduce a new variable:

If rngBig Is Nothing Then ... etc.

Puzzling, but I do trust your code. Just wondering.

Howard