Dear All:

I have a excel workbook which contains two worksheets. Sheet 1 is my
input page. Sheet 2 is my summary page.


When my BINARY input numbers in any row under column A which is under
sheet 1 are equal to 1, I would like the whole row to be copied to
sheet 2.
When my input numbers in any row under column A which is under sheet 1
are equal to 0, the whole row will NOT be copied to sheet 2.


However, I don't like any gaps in sheet 2 AND I would like to keep the
same order in sheet 2 as in sheet 1. In addition, I would like this to
be done in an automatic procedure. No autofilter or any other manual
efforts. The following is an illustration of what I would like to see:


Under sheet 1


Column A ColumnB Column C ......


row1 1 Text1 1
row2 0 Text2 2
row3 0 Text3 3
row4 1 Text4 4


Since there are inputs in cell A1 and A2, the whole row 1 and row 2
will be copied AUTOMATICALLY to sheet 2 in the same order WITHOUT any
gap as follows:


Under sheet 2


ColumnA Column B Column C
row1 1 Text1 1
row2 1 Text4 4


Anybody knows how to achieve this? It is my headache. I really
appreciate your help! Thank you so much and I am looking forward to
hearing from you soon!


George

You can do this with array formulas.

If you want a sample formula, send me a sample workbook at
and i will show you how.

--
Regards,
Tom Ogilvy



"George" wrote:

Dear All:

I have a excel workbook which contains two worksheets. Sheet 1 is my
input page. Sheet 2 is my summary page.


When my BINARY input numbers in any row under column A which is under
sheet 1 are equal to 1, I would like the whole row to be copied to
sheet 2.
When my input numbers in any row under column A which is under sheet 1
are equal to 0, the whole row will NOT be copied to sheet 2.


However, I don't like any gaps in sheet 2 AND I would like to keep the
same order in sheet 2 as in sheet 1. In addition, I would like this to
be done in an automatic procedure. No autofilter or any other manual
efforts. The following is an illustration of what I would like to see:


Under sheet 1


Column A ColumnB Column C ......


row1 1 Text1 1
row2 0 Text2 2
row3 0 Text3 3
row4 1 Text4 4


Since there are inputs in cell A1 and A2, the whole row 1 and row 2
will be copied AUTOMATICALLY to sheet 2 in the same order WITHOUT any
gap as follows:


Under sheet 2


ColumnA Column B Column C
row1 1 Text1 1
row2 1 Text4 4


Anybody knows how to achieve this? It is my headache. I really
appreciate your help! Thank you so much and I am looking forward to
hearing from you soon!


George

Offer withdrawn.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

You can do this with array formulas.

If you want a sample formula, send me a sample workbook at
and i will show you how.

--
Regards,
Tom Ogilvy



"George" wrote:

Dear All:

I have a excel workbook which contains two worksheets. Sheet 1 is my
input page. Sheet 2 is my summary page.


When my BINARY input numbers in any row under column A which is under
sheet 1 are equal to 1, I would like the whole row to be copied to
sheet 2.
When my input numbers in any row under column A which is under sheet 1
are equal to 0, the whole row will NOT be copied to sheet 2.


However, I don't like any gaps in sheet 2 AND I would like to keep the
same order in sheet 2 as in sheet 1. In addition, I would like this to
be done in an automatic procedure. No autofilter or any other manual
efforts. The following is an illustration of what I would like to see:


Under sheet 1


Column A ColumnB Column C ......


row1 1 Text1 1
row2 0 Text2 2
row3 0 Text3 3
row4 1 Text4 4


Since there are inputs in cell A1 and A2, the whole row 1 and row 2
will be copied AUTOMATICALLY to sheet 2 in the same order WITHOUT any
gap as follows:


Under sheet 2


ColumnA Column B Column C
row1 1 Text1 1
row2 1 Text4 4


Anybody knows how to achieve this? It is my headache. I really
appreciate your help! Thank you so much and I am looking forward to
hearing from you soon!


George

Hi George,

Explained he

http://office.microsoft.com/en-us/as...260381033.aspx


You'll be lookig up 1 in Column A and reurn multiple rows

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"George" wrote in message oups.com...
| Dear All:
|
| I have a excel workbook which contains two worksheets. Sheet 1 is my
| input page. Sheet 2 is my summary page.
|
|
| When my BINARY input numbers in any row under column A which is under
| sheet 1 are equal to 1, I would like the whole row to be copied to
| sheet 2.
| When my input numbers in any row under column A which is under sheet 1
| are equal to 0, the whole row will NOT be copied to sheet 2.
|
|
| However, I don't like any gaps in sheet 2 AND I would like to keep the
| same order in sheet 2 as in sheet 1. In addition, I would like this to
| be done in an automatic procedure. No autofilter or any other manual
| efforts. The following is an illustration of what I would like to see:
|
|
| Under sheet 1
|
|
| Column A ColumnB Column C ......
|
|
| row1 1 Text1 1
| row2 0 Text2 2
| row3 0 Text3 3
| row4 1 Text4 4
|
|
| Since there are inputs in cell A1 and A2, the whole row 1 and row 2
| will be copied AUTOMATICALLY to sheet 2 in the same order WITHOUT any
| gap as follows:
|
|
| Under sheet 2
|
|
| ColumnA Column B Column C
| row1 1 Text1 1
| row2 1 Text4 4
|
|
| Anybody knows how to achieve this? It is my headache. I really
| appreciate your help! Thank you so much and I am looking forward to
| hearing from you soon!
|
|
| George
|

Hi George
If I were you I would have filtered the rows that contain 1 and copy the
filtered range and paste it to the second sheet. The follovin code might do
the job:
Sub Test
dim rngCur as range ' Current range

Sheets("Sheet 1").activate
' assuming that you have data starting from A1
Range("A1").AutoFilter Field:=1, Criteria1:="1", Operator:=xlAnd
Set rngCur= range("A1").CurrentRegion
rngCur.Copy
Sheets("Sheet 2").activate
range("A1").Select
Selection.PasteSpecial Paste:= xlPasteAll
end sub

Ömer Ayzan

"George" wrote in message
oups.com...
Dear All:

I have a excel workbook which contains two worksheets. Sheet 1 is my
input page. Sheet 2 is my summary page.


When my BINARY input numbers in any row under column A which is under
sheet 1 are equal to 1, I would like the whole row to be copied to
sheet 2.
When my input numbers in any row under column A which is under sheet 1
are equal to 0, the whole row will NOT be copied to sheet 2.


However, I don't like any gaps in sheet 2 AND I would like to keep the
same order in sheet 2 as in sheet 1. In addition, I would like this to
be done in an automatic procedure. No autofilter or any other manual
efforts. The following is an illustration of what I would like to see:


Under sheet 1


Column A ColumnB Column C ......


row1 1 Text1 1
row2 0 Text2 2
row3 0 Text3 3
row4 1 Text4 4


Since there are inputs in cell A1 and A2, the whole row 1 and row 2
will be copied AUTOMATICALLY to sheet 2 in the same order WITHOUT any
gap as follows:


Under sheet 2


ColumnA Column B Column C
row1 1 Text1 1
row2 1 Text4 4


Anybody knows how to achieve this? It is my headache. I really
appreciate your help! Thank you so much and I am looking forward to
hearing from you soon!


George

Hi, Omer:

Thanks for your help! What I need is an automatic process. I do not
like to do it manually. The only thing I would like to see is whenever
I input 0 or 1 under column A on sheet 1, all rows with input 1 will go
to sheet 2 IN THE SAME ORDER.

I would appricate your efforts if you have an alternative to achieve
this.

George

Ömer Ayzan wrote:
Hi George
If I were you I would have filtered the rows that contain 1 and copy the
filtered range and paste it to the second sheet. The follovin code might do
the job:
Sub Test
dim rngCur as range ' Current range

Sheets("Sheet 1").activate
' assuming that you have data starting from A1
Range("A1").AutoFilter Field:=1, Criteria1:="1", Operator:=xlAnd
Set rngCur= range("A1").CurrentRegion
rngCur.Copy
Sheets("Sheet 2").activate
range("A1").Select
Selection.PasteSpecial Paste:= xlPasteAll
end sub

Ömer Ayzan

"George" wrote in message
oups.com...
Dear All:

I have a excel workbook which contains two worksheets. Sheet 1 is my
input page. Sheet 2 is my summary page.


When my BINARY input numbers in any row under column A which is under
sheet 1 are equal to 1, I would like the whole row to be copied to
sheet 2.
When my input numbers in any row under column A which is under sheet 1
are equal to 0, the whole row will NOT be copied to sheet 2.


However, I don't like any gaps in sheet 2 AND I would like to keep the
same order in sheet 2 as in sheet 1. In addition, I would like this to
be done in an automatic procedure. No autofilter or any other manual
efforts. The following is an illustration of what I would like to see:


Under sheet 1


Column A ColumnB Column C ......


row1 1 Text1 1
row2 0 Text2 2
row3 0 Text3 3
row4 1 Text4 4


Since there are inputs in cell A1 and A2, the whole row 1 and row 2
will be copied AUTOMATICALLY to sheet 2 in the same order WITHOUT any
gap as follows:


Under sheet 2


ColumnA Column B Column C
row1 1 Text1 1
row2 1 Text4 4


Anybody knows how to achieve this? It is my headache. I really
appreciate your help! Thank you so much and I am looking forward to
hearing from you soon!


George

Hi George,
Do you have row before you enter either 0 or one 1 into column A of that
row. If so you can write code for Worksheet_Change
event. In that event you check the content of the cell that is changed if it
is 1 than you copy the contents and than switch to sheet2 and paste whatever
you coppied. This scheme assumes that you just copy one row whose first cell
is the one you just edited.

You can use the following code: However you need to take the necessary
precautions not to copy the same row more than once. Cause change event
fires whenever you leave the current cell

Omer

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bytData As Byte
Dim intCol As Integer
Dim lngRow As Long
Dim rngCur As Range

With Target
intCol = .Column
If intCol < 1 Then Exit Sub ' We dont want todo any thing other
then first column
lngRow = .Row
End With

bytData = Target.Value

If bytData = 1 Then
Set rngCur = Range("A1").CurrentRegion.Rows(lngRow) ' Assumin g you
have data startin from a1
rngCur.Copy
CopyToMySht "Sheet2", "A1", xlPasteAll ' This always adds after
the end row of currentregion
End If
End Sub

Sub CopyToMySht(mySht As String, _
Off_Set As String, pMethode As XlPasteType)
Dim rCnt As Long
Dim rngPaste As Range


With Sheets(mySht)
If .Range(Off_Set) < "" Then
rCnt = .Range(Off_Set).CurrentRegion.Rows.Count
Set rngPaste = .Range(Off_Set).Offset(rCnt)
Else
Set rngPaste = .Range(Off_Set)
End If
End With

rngPaste.PasteSpecial Paste:=pMethode, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

Application.CutCopyMode = False

End Sub

"George" wrote in message
oups.com...
Hi, Omer:

Thanks for your help! What I need is an automatic process. I do not
like to do it manually. The only thing I would like to see is whenever
I input 0 or 1 under column A on sheet 1, all rows with input 1 will go
to sheet 2 IN THE SAME ORDER.

I would appricate your efforts if you have an alternative to achieve
this.

George

Ömer Ayzan wrote:
Hi George
If I were you I would have filtered the rows that contain 1 and copy the
filtered range and paste it to the second sheet. The follovin code might
do
the job:
Sub Test
dim rngCur as range ' Current range

Sheets("Sheet 1").activate
' assuming that you have data starting from A1
Range("A1").AutoFilter Field:=1, Criteria1:="1", Operator:=xlAnd
Set rngCur= range("A1").CurrentRegion
rngCur.Copy
Sheets("Sheet 2").activate
range("A1").Select
Selection.PasteSpecial Paste:= xlPasteAll
end sub

Ömer Ayzan

"George" wrote in message
oups.com...
Dear All:

I have a excel workbook which contains two worksheets. Sheet 1 is my
input page. Sheet 2 is my summary page.


When my BINARY input numbers in any row under column A which is under
sheet 1 are equal to 1, I would like the whole row to be copied to
sheet 2.
When my input numbers in any row under column A which is under sheet 1
are equal to 0, the whole row will NOT be copied to sheet 2.


However, I don't like any gaps in sheet 2 AND I would like to keep the
same order in sheet 2 as in sheet 1. In addition, I would like this to
be done in an automatic procedure. No autofilter or any other manual
efforts. The following is an illustration of what I would like to see:


Under sheet 1


Column A ColumnB Column C ......


row1 1 Text1 1
row2 0 Text2 2
row3 0 Text3 3
row4 1 Text4 4


Since there are inputs in cell A1 and A2, the whole row 1 and row 2
will be copied AUTOMATICALLY to sheet 2 in the same order WITHOUT any
gap as follows:


Under sheet 2


ColumnA Column B Column C
row1 1 Text1 1
row2 1 Text4 4


Anybody knows how to achieve this? It is my headache. I really
appreciate your help! Thank you so much and I am looking forward to
hearing from you soon!


George