copy insert problem

[saziz]

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

 

[DavidC]

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

 

[saziz]

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

 

[DavidC]

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

 

[saziz]

David, it worked perfectly.
Thanks a bunch
Sazi