View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Dibben Gord Dibben is offline
external usenet poster
 
Posts: 22,906
Default How to copy rows that meet criteria to another sheet in Excel

Bruce

Sub Copy_To_Sheet()
Dim i As Integer
Dim wks As Worksheet
Dim iLastRow As Long
Set wks = ActiveSheet
Application.ScreenUpdating = False
iLastRow = wks.Cells(Rows.Count, 3).End(xlUp).Row
For i = iLastRow To 1 Step -1
If wks.Cells(i, 3).Value = 1 Then
wks.Rows(i).Copy Destination:= _
Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
End If
Next i
Application.ScreenUpdating = True
End Sub

You could call this from event code like BeforeClose or BeforeSave or
WorksheetActivate or Deactivate


Gord Dibben MS Excel MVP

On Thu, 2 Nov 2006 11:38:01 -0800, Bruce
wrote:

In one worksheet, I have a set of about 15 columns. For those rows that have
a value of 1 in column 3, I want to copy those to another worksheet. Both
worksheets are in the same workbook.

My current # of rows is only 170. I want sheet #2 to automatically grow as
things are added to sheet 1.

How?