The macro has two counter variables. i keeps track of the current row number
of the source worksheet while j keeps track of the current row number of the
destination worksheet.

Range(i & ":" & i).Cut Sheets("Chrg").Range("A" & j)
If the specified criteria in the If statement is met, the above line will
cut that entire row and paste it into the "Chrg" worksheet. The syntax is
Source.Cut Destination where source and destination are range references.

j=j+1 just increments the counter variable to identify the row number that
the next line of data should go into on your destination worksheet.


"Jentan" wrote:

Thank you. May I know the meaning of this :

Range(i & ":" & i).Cut Sheets("Chrg").Range("A" & j)
j = j + 1


JMB wrote:
This seemed to work for me. Count returns a count of numeric data in column
10, so it may not be the same number as the last row. Also, you need to
initial j, otherwise I am pretty sure VBA will assume the initial value to be
0, which is an invalid row reference. Backup before trying.

Sub test()
lastrow = Cells(Rows.Count, 10).End(xlUp).Row
j = 1

For i = 1 To lastrow
If Cells(i, 3) = "INEXC" Or Cells(i, 3) = "RINEXC" _
Or Cells(i, 3) = "RTNCHQSR" Or Cells(i, 3) = "RTNCHQSC" _
Then
Range(i & ":" & i).Cut Sheets("Chrg").Range("A" & j)
j = j + 1
End If
Next i
End Sub


"Jentan" wrote:


I am trying to categorise different information into different sheets
within the same file.

A B C D E
1 000514329214734 29/06/2006 INEXC 0740 INLAND EXCHANGE COMM

2 000514329214734 29/06/2006 7100 0740 424280606
*
3 000514329214734 29/06/2006 RINEXC 0740 INLAND EXCHANGE COMM

4 000514329214734 29/06/2006 7100 0740 424290606
*
5 000514329214734 29/06/2006 RTNCHQSR 0740 INLAND EXCHANGE COMM

6 000514329214734 29/06/2006 7100 0740 424290606
*
7 000514329214734 29/06/2006 INEXC 0105 INLAND EXCHANGE COMM



I have using the following macro command but somehow or rather it does
not work. I wanted to transfer all the "INEXC", "RINEXC","RTNCHQSR" in
column C to another new sheet named as "Chrg". Is my command wrong?

lastrow = WorksheetFunction.Count(Columns(10))
For i = 1 To lastrow

If Cells(i, 3) = "INEXC" Or Cells(i, 3) = "RINEXC" _
Or Cells(i, 3) = "RTNCHQSR" Or Cells(i, 3) = "RTNCHQSC" _
Then
Range(i & ":" & i).Cut Sheets("Chrg").Range("A" & j)
j = j + 1
End If

Appreciate for assistance