Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
Copy an additional row based on another sheet | Excel Discussion (Misc queries) | |||
Clicking Cell Link Changes Cell on Another Sheet | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
how do i copy formula and change worksheet instead of cell | Excel Worksheet Functions |