Automatically Move Entire Row to Different worksheet

  • Thread starter Thread starter George
  • Start date Start date
G

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
 
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
 
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
 
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
 
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.
 
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
 
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
 
Back
Top