Copy a range from one copy to the other

[Jentan]

I need to copy the data from "SP" tab to the "Form 295" tab based on a
certain criteria. The criterias are if column 6 is "Y" and column 7 is
"N", then transfer to "Form 295" tab.


1 2 3 4 5 6 7
2 Tab: SP tab
3 masno cust_name ext_limit exp_dt commited UFN
4 0000158 MARIDAH BINTI DATO'IDRIS 42000 30.11.18 Y N
5 0000158 MARIDAH BINTI DATO'IDRIS 1201 30.11.18 Y N
6 0000521 LIM PANG LOONG 300000 31.08.06 N Y
7
8
9 Tab: Form295
10 masno cust_name ext_limit exp_dt commited UFN


Below is the macro that I have created but it doesn't seems to work at
all.

Sub Copy_SPtab()

Dim lastrow As Double, i As Double, j As Double

Sheets("Form295").Cells.ClearContents

lastrow = WorksheetFunction.Count(Columns(7))
i = 1
j = 1
For i = 2 To lastrow


If Cells(i, 9) = "Y" And Cells(i, 10) = "N" Then
Range(i & ":" & i).Copy Sheets("Form295").Range("A" & j)
j = j + 1
End If
Next


End Sub

[Simon Lloyd]

without using your code i notice that in your IF THEN statement you specify a range to copy and directly after specify a destination but no selection or paste command.

Regards,
Simon

[Jentan]

Hi,

I think there isn't any problem with the copy paste formula. I have
used the same logic for others. The logic is as below.

Range("A1:E1").copy range("A2")

Appreciate if you can assist to list down the correct formulaes.

Thank you.


Simon Lloyd wrote:
> without using your code i notice that in your IF THEN statement you
> specify a range to copy and directly after specify a destination but no
> selection or paste command.
>
> Regards,
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=572036

[[email protected]]

hI JEN,

Just +1 to lastrow ... maybe helpfull for you ...

Thanks,

Halim

Jentan menuliskan:
> Hi,
>
> I think there isn't any problem with the copy paste formula. I have
> used the same logic for others. The logic is as below.
>
> Range("A1:E1").copy range("A2")
>
> Appreciate if you can assist to list down the correct formulaes.
>
> Thank you.
>
>
> Simon Lloyd wrote:
> > without using your code i notice that in your IF THEN statement you
> > specify a range to copy and directly after specify a destination but no
> > selection or paste command.
> >
> > Regards,
> > Simon
> >
> >
> > --
> > Simon Lloyd
> > ------------------------------------------------------------------------
> > Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
> > View this thread: http://www.excelforum.com/showthread...hreadid=572036