Solved: selecting and copying a range where a variable is found

[sleepy_think]

Heya folks,

problem:

I have a "checklist" worksheet and a "data" worksheet. I need to take a value in the checklist worksheet (located on range("b2")) search the data sheet in range from ("k11:k5693") for the match up, then copy the range that finds that data from column c to k (but only that particular row no others).

heres what i have so far:

Dim usid As String
Set usid = Range("b2")
Range("C2").Select
Workbooks.Open Filename:="x:\data.xls"

Dim DATS As Range, Mycell As Range
DATS = Range("K11:K5693")
For Each Mycell In DATS

With Mycell

Select Case True
Case .Value = usid


End Select
End With
Next Mycell

thanks in advance!

[Bob Phillips]

Dim usid As String
Dim cell As Range

Set usid = Range("B2")
Range("C2").Select
Workbooks.Open Filename:="x:\data.xls"

Dim DATS As Range, Mycell As Range
Set DATS = Range("K11:K5693")

'not sure where the two worksheets comes into it!!!!!!!!!!!!!

Set cell = DATS.Find(usid)
If Not cell Is Nothing Then

Cells(cell.Row, "C").Resize(, 9).Copy

'and do what to it
End If

[sleepy_think]

sorry i should be more specific:

worksheet A) is where i want to paste my data (Checklist.xls)
Worksheet B) is where the data is (data.xls)

I would be currently in the Checklist when i run this vba.

I need to find the variable(from the checklist) in the data.xls and then copy that row (and only that row) back to the checklist...

does that help?

thx btw

[sleepy_think]

hey xld, your code worked ... is there a command to paste the values in cells through? one of them is returning a function.

thanks !

[sleepy_think]

hey xld, your code worked ... is there a command to paste the values in cells through? one of them is returning a function.

thanks !

[sleepy_think]

got it!

ActiveSheet.Range("c2").PasteSpecial Paste:=xlPasteValues


thanks for your help again!