Modifying to use range

[thomassharp]

Hi is to possible to modify the code to use a range of values from another sheet in the workbook instead of a hardcoded value?

Sub Macro6()
'
' Macro6 Macro
'


'
    Columns("E:E").Select
    Selection.find(What:="value", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Rows("8702:8702").Select
    Range("C8702").Activate
    Selection.Copy
    Sheets("Sheet4").Select
    Rows("2:2").Select
    ActiveSheet.Paste
End Sub

 

[SteveO59L]

try posting a sample of your data, and what you would like to do with it

 

[thomassharp]

try posting a sample of your data, and what you would like to do with it

I have a range with values and want to use those values to search in another sheet then copy the row if found and paste to another sheet. Range of values is in Row M Sheet2, and I want to perform a find in column E of Sheet1, using that range, then if the value exists in column E, paste entire row into sheet3 and move onto next value.
Here is a sample version of the workbook (I used the html add in but I was worried about pasting all that html into a message but please tell me if I should)
https://www.dropbox.com/s/uxs5mky0f8jc7eb/sampledata%20%281%29.xlsx?dl=0

 

[SteveO59L]

row M ?

 

[thomassharp]

Column M sorry

 

[SteveO59L]

something along the lines of

Dim c1 As Range, rng1
Dim c2 As Range, rng2
Dim lastrow As Long
Set rng1 = Range("sheet1!a1:a10")
Set rng2 = Range("sheet2!a1:a10")



For Each c1 In rng1
For Each c2 In rng2
If c1 = c2 Then
c2.EntireRow.Copy
Sheets("sheet3").Activate
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
Range("a" & lastrow + 1).Select
ActiveSheet.Paste
End If
Next c2
Next c1

 

[thomassharp]

HI, thanks very much for looking at this. When I run it with my values it runs down indefinitely until I stop it. How can I set it to stop when it reaches the bottom of the range from sheet2?

Sub findvalues()


Dim c1 As Range, rng1
Dim c2 As Range, rng2
Dim lastrow As Long
Set rng1 = Range("sheet1!m1:m50")
Set rng2 = Range("sheet2!e1:e50")






For Each c1 In rng1
For Each c2 In rng2
If c1 = c2 Then
c2.EntireRow.Copy
Sheets("sheet3").Activate
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
Range("a" & lastrow + 1).Select
ActiveSheet.Paste
End If
Next c2
Next c1


End Sub

 

[SteveO59L]

you shouldn't need to stop it, should just reach the end of the range you specified

send me a pm with your email addy and I will send you a sample sheet