Retrieving Values from Cell with Macro

[Anift]

Hi I want to write a macro code to retrieve the value in a Cell in Range( B1
: B20) if the corresponding Value in C1:C20 is " Open".
The Value should be displayed in a Range ( A1: A20) in a differnt worksheet
of the same file

For E.G B1 = Name1 C1 = Closed
B2 = Name2 C2 = Open
B3 = Name3 C3= Open
...... ......
...... ......

Now in Other sheet, I want to import all the values of Cell in range B1:20
if the corresponding value in Cell range C1:C20 is " Open"

Can anybody help with the code?, any help will be appreciated

 

[Bob Phillips]

For Each cell In Worksheets("Sheet1").Range("B1:B20")
If cell.Offset(0,1).Value = "Open" Then
i = i + 1
cell.copy Worksheets("Sheet2").Cells(i,"A").Value
End If
Next cell

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Anift]

HI Bob,
Thanks for the reply,
It throwing an error "object doesnt support property or method " on
cell.copy Worksheets("Sheet2").Cells(i,"A").Value

any suggestions?

 

[Dave Peterson]

Try dropping that .Value at the end of the "cell.copy ..." line.

 

[Anift]

Thanks Dave,
that worked, Thanks to Bob as well.

You guys Rock