thanks for your reply sorry for being late in my response
shall i use this through VB in excel?

also please advise is there any other option like following formula.
=INDEX(Sheet1!$A$2:$IV$4000, ???????????)

thanks for your continued support/help

regards
Malik Nadeem


"Joel" wrote:

Try this code

Sub movedata()

Sh2RowCount = 1
With Sheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set ReflectRange = .Range("F1:F" & LastRow)

For Each cell In ReflectRange
If cell.Value = "PANDING" Then
.Rows(cell.Row).Copy _
Destination:=Sheets("Sheet2"). _
Rows(Sh2RowCount)
Sh2RowCount = Sh2RowCount + 1
End If
Next cell
End With

End Sub

"Malik Nadeem" wrote:

How can I transfer a required / certain data from sheet one to another sheet
two automatically in a same file

example

SHEET ONE

A B C D E F €¦€¦€¦€¦€¦€¦€¦€¦.
1 4550 1000 APRIL 15000 4550 PANDING
2 8550 2000 DEC 1000 2000
3 6750 5555 JAN 6000 111 PANDING
4 4550 1000 APRIL 15000 4550 PANDING
5 8550 2000 DEC 1000 2000
6 6750 5555 JAN 6000 111 PANDING
.
.
.
.

I want to shift only that line which F column is reflecting = Panding.

Like result on sheet two in following condition
A B C D E F €¦€¦€¦€¦€¦€¦€¦€¦.
1 4550 1000 APRIL 15000 4550 PANDING
3 6750 5555 JAN 6000 111 PANDING
4 4550 1000 APRIL 15000 4550 PANDING
6 6750 5555 JAN 6000 111 PANDING
.
.
.
.

quick response will be highly appreciated.

Regards
Malik Nadeem
Karachi - Pakistan
cell # 0092 300 2556512