Automatically Move Entire Row to Different worksheet

[George]

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

 

[Don Guillett]

open issues sheet>Right click sheet tab>view code>insert 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

 

[Gord Dibben]

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

 

[George]

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

 

[Don Guillett]

I think, in both cases, that the data is moved to the last available row +1
and the old row is DELETED.
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Gord Dibben]

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

 

[Don Guillett]

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