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

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

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

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

Automatically Move Entire Row to Different worksheet
 

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.



--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"George" wrote in message
...
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

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

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