Hello All,
When I run this macro it gives me "copy mehtod of range class failed"
Can someone pls help me. All I doing is to let it get the ranges an
insert in order form at range A12.
I have Excel2000

Saziz

Sheets("Mixing Coating").Select
Set rng = ActiveSheet.Range("H12:H25")
For Each cell In rng

If cell.Value < "" And cell.Value < 5 Then
cell.Offset(0, -7).Resize(1, 4).Copy _
Worksheets("OrderForm").Range("A12").Insert





End If
Nex

--
Message posted from http://www.ExcelForum.com

First you donot need the insert at the end of the copy
statement. Then surely you need to shift the cell where
the data is pasted to after each iteration otherwise the
new data simply overwrites the data in A12. Hence I have
added a variable a =12 to start at A12 then increment that
by 1 to then goto A13 A14 and so on.

Sheets("Mixing Coating").Select
Set rng = ActiveSheet.Range("H12:H25")
a=12

For Each cell In rng

If cell.Value < "" And cell.Value < 5 Then
cell.Offset(0, -7).Resize(1, 4).Copy _
(Worksheets("OrderForm").Range("A"&a))


End If

a=a+1

Next

Hope this helps

DavidC

-----Original Message-----
Hello All,
When I run this macro it gives me "copy mehtod of range
class failed"
Can someone pls help me. All I doing is to let it get
the ranges and
insert in order form at range A12.
I have Excel2000

Saziz

Sheets("Mixing Coating").Select
Set rng = ActiveSheet.Range("H12:H25")
For Each cell In rng

If cell.Value < "" And cell.Value < 5 Then
cell.Offset(0, -7).Resize(1, 4).Copy _
Worksheets("OrderForm").Range("A12").Insert





End If
Next


---
Message posted from http://www.ExcelForum.com/

.

David,
Thanks for ur help, Its working. But it also is creating empty ro
after each iteration. I guess because we gave the incriment a= a+1.
So I need to insert a code to delete empty rows. How would I do that?
Sazi

--
Message posted from http://www.ExcelForum.com

No need to delete any rows. I just had the a=a+1 in the
wrong place. Try this code below:

Sheets("Mixing Coating").Select
Set rng = ActiveSheet.Range("H12:H25")
a = 12

For Each cell In rng

If cell.Value < "" And cell.Value < 5 Then
cell.Offset(0, -7).Resize(1, 4).Copy _
(Worksheets("OrderForm").Range("A" & a))

a = a + 1

Else

End If

Next

Best of luck
DavidC
-----Original Message-----
David,
Thanks for ur help, Its working. But it also is
creating empty row
after each iteration. I guess because we gave the
incriment a= a+1.
So I need to insert a code to delete empty rows. How
would I do that?
Saziz


---
Message posted from http://www.ExcelForum.com/

.

David, it worked perfectly.
Thanks a bunch
Sazi

--
Message posted from http://www.ExcelForum.com