Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Move Entire Row to Different worksheet
Good Morning,
After reviewing the postings similar to my need I haven't quite found the help I've needed...So here's my question I have two worksheets "Open issues" & "Closed Issues", Using the "Open issue", sheet if the Value in Column "A" is changed to "Closed" I'd then like that entire row automatically moved to next empty row in the "Closed Issues" sheet... Thanks In Advance, George |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Move Entire Row to Different worksheet
open issues sheetRight click sheet tabview codeinsert this
Now, whenever you type closed in col A the row will be moved Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 1 Then Exit Sub Application.EnableEvents = False If LCase(Target) = "closed" Then With Sheets("closed issues") lr = .Cells(Rows.Count, 1).End(xlUp).Row + 1 Target.EntireRow.Cut Destination:=.Cells(lr, 1) End With End If Application.EnableEvents = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "George" wrote in message ... Good Morning, After reviewing the postings similar to my need I haven't quite found the help I've needed...So here's my question I have two worksheets "Open issues" & "Closed Issues", Using the "Open issue", sheet if the Value in Column "A" is changed to "Closed" I'd then like that entire row automatically moved to next empty row in the "Closed Issues" sheet... Thanks In Advance, George |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Move Entire Row to Different worksheet
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng1 As Range Dim rng2 As Range Set rng1 = Target.EntireRow Set rng2 = Worksheets("Closed Issues").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) If Target.Column = 1 Then On Error GoTo endit Application.EnableEvents = False If Target.Value = "Closed" Then With rng1 .Copy Destination:=rng2 .Delete End With End If End If endit: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Mon, 25 Jan 2010 07:52:01 -0800, George wrote: Good Morning, After reviewing the postings similar to my need I haven't quite found the help I've needed...So here's my question I have two worksheets "Open issues" & "Closed Issues", Using the "Open issue", sheet if the Value in Column "A" is changed to "Closed" I'd then like that entire row automatically moved to next empty row in the "Closed Issues" sheet... Thanks In Advance, George |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Move Entire Row to Different worksheet
Thank You Gents, They work great I just have two small issues...
When row is deleted from the "Open Issues" sheet it leaves an empty row I don't need Secondly, When the row is placed in the "Closed issues" sheet its inserted at the top and I'd like it to be inserted at the botton. Thanks Again "George" wrote: Good Morning, After reviewing the postings similar to my need I haven't quite found the help I've needed...So here's my question I have two worksheets "Open issues" & "Closed Issues", Using the "Open issue", sheet if the Value in Column "A" is changed to "Closed" I'd then like that entire row automatically moved to next empty row in the "Closed Issues" sheet... Thanks In Advance, George |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Move Entire Row to Different worksheet
With my code the row is copied to next available blank row in Closed Issues
then original row deleted with no empty row left behind. Don's code leaves an empty row due to Target.EntireRow.Cut Destination:=.Cells(lr, 1) Gord On Mon, 25 Jan 2010 10:48:01 -0800, George wrote: Thank You Gents, They work great I just have two small issues... When row is deleted from the "Open Issues" sheet it leaves an empty row I don't need Secondly, When the row is placed in the "Closed issues" sheet its inserted at the top and I'd like it to be inserted at the botton. Thanks Again "George" wrote: Good Morning, After reviewing the postings similar to my need I haven't quite found the help I've needed...So here's my question I have two worksheets "Open issues" & "Closed Issues", Using the "Open issue", sheet if the Value in Column "A" is changed to "Closed" I'd then like that entire row automatically moved to next empty row in the "Closed Issues" sheet... Thanks In Advance, George |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Move Entire Row to Different worksheet
Gord is correct. If using mine change to Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 1 Then Exit Sub Application.EnableEvents = False If LCase(Target) = "closed" Then With Sheets("sheet8") lr = .Cells(Rows.Count, 1).End(xlUp).Row + 1 Target.EntireRow.Copy Destination:=.Cells(lr, 1) Target.EntireRow.Delete End With End If Application.EnableEvents = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Gord Dibben" <gorddibbATshawDOTca wrote in message ... With my code the row is copied to next available blank row in Closed Issues then original row deleted with no empty row left behind. Don's code leaves an empty row due to Target.EntireRow.Cut Destination:=.Cells(lr, 1) Gord On Mon, 25 Jan 2010 10:48:01 -0800, George wrote: Thank You Gents, They work great I just have two small issues... When row is deleted from the "Open Issues" sheet it leaves an empty row I don't need Secondly, When the row is placed in the "Closed issues" sheet its inserted at the top and I'd like it to be inserted at the botton. Thanks Again "George" wrote: Good Morning, After reviewing the postings similar to my need I haven't quite found the help I've needed...So here's my question I have two worksheets "Open issues" & "Closed Issues", Using the "Open issue", sheet if the Value in Column "A" is changed to "Closed" I'd then like that entire row automatically moved to next empty row in the "Closed Issues" sheet... Thanks In Advance, George |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically move data from one worksheet to another | Excel Discussion (Misc queries) | |||
Move entire row if it contains specified text. | Excel Discussion (Misc queries) | |||
Move cell data to another worksheet cell automatically. | New Users to Excel | |||
Move entire row to another worksheet based on drop list selection | Excel Worksheet Functions | |||
automatically move row to another worksheet | Excel Discussion (Misc queries) |