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

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

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

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