VBA Code...

M

MarkHear1

Hi all,

I want to write a VBA (Excel) code to do the following...
Recognise when "Yes" is typed into any cell in column "C" on "sheet1"
and then cut that row, and paste it into the first empty row on
"sheet2", and finally delete the row on "sheet1".
Can anybody offer any suggestions/code as to how I can achieve this?


Many thanks,
Mark
 
B

Bob Phillips

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C:C" '<== change to suit
Dim iLastRow As Long

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Count = 1 Then
If .Value = "Yes" Then
iLastRow = Worksheets("Sheet2").Cells(Rows.Count,
"A").End(xlUp).Row
If iLastRow > 1 Or
Worksheets("Sheet2").Range("A1").Value <> "" Then
iLastRow = iLastRow + 1
End If
.EntireRow.Copy Worksheets("Sheet2").Cells(iLastRow,
"A")
.EntireRow.Delete
End If
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

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Top