Move a row to a different sheet, dependant upon cell value

[Fred Newton]

We have just set up an Excel based "Issues Log" to record issues
arising against projects currently in progress. The Issues log has two
worksheets, Issues and Completed Issues and each issue has several
fields, one of which is its current Status. What I would like to
achieve is, when the status is set to Complete (from a Data/Validation
drop-down list), the Issue in question is moved to the Completed
Issues worksheet. I have seen several items describing how to run a
macro based on a cell value, but they all seem quite specific in the
cell they are looking at. Is there a way to base the macro activation
upon a change to a cell in one column instead, while only affecting
one row of data ?

Many thanks
Fred Newton

 

[Bob Phillips]

Hi Fred,

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cNextRow As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
With Target
If .Value = "Complete" Then
cNextRow = Worksheets("Sheet2").Cells(Rows.Count,
"A").End(xlUp).Row + 1
.EntireRow.Copy Worksheets("Sheet2").Cells(cNextRow, "A")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Fred Newton]

Thanks Bob, that worked a treat. One final question however, how do I
get it to insert at a specific point in the "Completed" sheet (your
Sheet2) rather than paste at the end? The reason for this is that
there are "Sort" macros on the "Completed" sheet that work based on a
range and that range is bounded by the headers and an "insert before
this line" marker.

Thanks again
Fred

 

[Bob Phillips]

Fred,

How would you identify that position so that the code would know?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Fred Newton]

Hi Bob,

The moved line would be placed after the last line to have been moved,
or the column headers, always in row 6, but before the "insert before
this line" marker, cell A, row any, but minimum of row 7, i.e. no
completed items yet.

I have modified the code to remove the line following the copy into
the "Completed" sheet, see below, but had no joy modifying the
xlUp.Row to insert before the marker, it just pastes over whatever is
there.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cNextRow As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("H1:H100")) Is Nothing Then
With Target
If .Value = "Completed" Or .Value = "completed" Then
cNextRow = Worksheets("Completed").Cells(Rows.Count,
"A").End (xlUp).Row + 1
.EntireRow.Copy Worksheets("Completed").Cells(cNextRow,
"A")
.EntireRow.Delete
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Thanks again for your help
Regards
Fred Newton

 

[Fred Newton]

Hi Bob,
On re-reading your question I now understand what you are asking. The
titles row and the "insert above this line" row both have been "named"
as "Print Titles" and "BottomRow", thereby allowing the sort to know
where to start/stop.

Regards
Fred