Find, copy then paste problem
 

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

Find, copy then paste problem
 

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

skrev i meddelelsen
...
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

Find, copy then paste problem
 

On May 19, 1:54 am, "Per Jessen" wrote:
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