Retrieving Values from Cell with Macro
 

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

Retrieving Values from Cell with Macro
 

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" wrote in message
...
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

Retrieving Values from Cell with Macro
 

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?

"Bob Phillips" wrote:

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" wrote in message
...
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

Retrieving Values from Cell with Macro
 

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

Anift wrote:

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?

"Bob Phillips" wrote:

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" wrote in message
...
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





--

Dave Peterson

Retrieving Values from Cell with Macro
 

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

You guys Rock


"Anift" wrote:

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