Calling a macro

[Michelle Hillard]

Hi guys,

I have this:

Sheet 1

Date Task Details Completed
y

What I want to do is when I put a 'y' in the completed column, it
automatically goes to a new sheet and appends to the end of existing rows.
The following macro works but I have to run it every time I want to move
these rows.
Is there any way to call it automatically to move these rows?

Module1

Public Sub MoveRowsWithYinColumnD()
Dim rDest As Range
Dim rSource As Range
Dim rCell As Range

Set rDest = Sheets("Sheet2").Range("A" & _
Rows.Count).End(xlUp).Offset(1, 0)
With Sheets("Sheet1")
For Each rCell In .Range("D1" & _
.Range("D" & Rows.Count).End(xlUp).Row)
If LCase(rCell.Value) = "y" Then
If rSource Is Nothing Then
Set rSource = rCell
Else
Set rSource = Union(rSource, rCell)
End If
End If
Next rCell
End With
If Not rSource Is Nothing Then
With rSource.EntireRow
.Copy rDest
.Delete
End With
End If
End Sub


Any help greatly appreciated.

--


---------------------------------------------------------------------
"Are you still wasting your time with spam?...
There is a solution!"

Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.com

 

[J.E. McGimpsey]

One way:

Use an Event Macro. Put this in the worksheet code module
(right-click on the worksheet tab and choose View Code):


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If .Column = 4 Then
If LCase(.Value) = "y" Then
With .EntireRow
.Copy Destination:= _
Sheets("Sheet2").Cells(Rows.Count, _
1).End(xlUp).Offset(1, 0)
.Delete
End With
End If
End If
End With
End Sub

 

[Michelle Hillard]

Guys I have worked it out, by using the Call method.
Thanks in advance.

--


---------------------------------------------------------------------
"Are you still wasting your time with spam?...
There is a solution!"

Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.com