Find, copy then paste problem

[kangas.alan]

This will probably be very simple for some of you, but it's got me
stumped.

I have a 3 sheet workbook, sheet three generates 10 random numbers
between 1 & 300. Sheet 2 has three pivot tables (different
departments) linked form other workbooks with column A listing a
number from 1 - 300. I need to find the generated numbers from sheet 3
on sheet 2 and then copy the matching row to sheet 1 for printing.
This is for the safety dept to perform random drug tests as honestly
random as possible.

Thanks in advance or any assistance.
Alan

 

[Per Jessen]

Hi Alan

Look at this macro

Sub Find_Copy()

Sheets("Sheet3").Select
StartCell = "A1" ' First Cell with random number

For c = 0 To 9
rNum = Range(StartCell).Offset(c, 0).Value
With Sheets("Sheet2")
Set found = .Columns("A").Find(what:=rNum)
.Rows(found.Row).Copy Sheets("Sheet1").Range("A2").Offset(off, 0)
off = off + 1
End With
Next
End Sub

Regards,
Per

 

[Alan]

Hi Alan

Look at this macro

Sub Find_Copy()

Sheets("Sheet3").Select
StartCell = "A1" ' First Cell with random number

For c = 0 To 9
rNum = Range(StartCell).Offset(c, 0).Value
With Sheets("Sheet2")
Set found = .Columns("A").Find(what:=rNum)
.Rows(found.Row).Copy Sheets("Sheet1").Range("A2").Offset(off, 0)
off = off + 1
End With
Next
End Sub

Regards,
Per

Thanks, that looks like it will do it. I may later look at other
things for this, but that's another day.

I never did work with VBA much, I can take something similar to what I
want and modify it but ...

Thanks again.
Alan