Copy row to a sheet if one cell has certain value
 

I'd like to enter a word in a cell (for instance, Closed) and have that entry
trigger the entire row being copied to another row in another sheet (w/in the
same workbook). How do I do that?

Copy row to a sheet if one cell has certain value
 

LeAnne

You could use sheet event code.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value = "closed" Then
Target.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A1")
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code"

Paste the above code into that module.

Type closed or Closed or CLOSED into A1 and the row will be copied to Sheet2
row 1


Gord Dibben Excel MVP

On Thu, 17 Nov 2005 11:20:11 -0800, "LeAnne S" <LeAnne
wrote:

I'd like to enter a word in a cell (for instance, Closed) and have that entry
trigger the entire row being copied to another row in another sheet (w/in the
same workbook). How do I do that?

Copy row to a sheet if one cell has certain value
 

Thanks, I'll try that.

"Gord Dibben" wrote:

LeAnne

You could use sheet event code.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value = "closed" Then
Target.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A1")
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code"

Paste the above code into that module.

Type closed or Closed or CLOSED into A1 and the row will be copied to Sheet2
row 1


Gord Dibben Excel MVP

On Thu, 17 Nov 2005 11:20:11 -0800, "LeAnne S" <LeAnne
wrote:

I'd like to enter a word in a cell (for instance, Closed) and have that entry
trigger the entire row being copied to another row in another sheet (w/in the
same workbook). How do I do that?

Copy row to a sheet if one cell has certain value
 

I tried this and it worked. However, there is one (or two) small problems.
I would like multiple rows copied to a common sheet in the same workbook.
What do I change the line Target.EntireRow.Copy
Destination:=Sheets("Sheet2").Range("A1") to make sure the rows from the
previous sheets are not overwriting themselves in A1 of the destination
sheet? For instance, if I have 7 rows to be copied from one sheet and go to
a summary sheet, how do I make sure those 7 rows are copied to the summary
sheet in rows 1-7?

Thanks,
LeAnne

"Gord Dibben" wrote:

LeAnne

You could use sheet event code.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value = "closed" Then
Target.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A1")
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code"

Paste the above code into that module.

Type closed or Closed or CLOSED into A1 and the row will be copied to Sheet2
row 1


Gord Dibben Excel MVP

On Thu, 17 Nov 2005 11:20:11 -0800, "LeAnne S" <LeAnne
wrote:

I'd like to enter a word in a cell (for instance, Closed) and have that entry
trigger the entire row being copied to another row in another sheet (w/in the
same workbook). How do I do that?