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

One answer in the m.p.excel newsgroup.

In article om,
"MarkHear1" wrote:

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

Thie code below should be placed in the code for worksheet1. go to Worksheet
page and rigght click on Sheet1 Tab and select code. the code will only be
called when there arre changes on sheett 1.

"Joel" wrote:

try this code

Private Sub Worksheet_Change(ByVal Target As Range)


Charcount = 1
If Target.Column < Range("C1").Column Then Exit Sub

If StrComp(StrConv(Target, vbUpperCase), "YES") < 0 Then Exit Sub

Set FirstRange = Target.EntireRow


FirstRange.Select
FirstRange.Cut


Worksheets("Sheet2").Select
RowOffset = 0

Do While StrComp(Worksheets("Sheet2").Range("a1"). _
Offset(RowOffset:=RowOffset, columnoffset:=0), "") < 0

RowOffset = RowOffset + 1
Loop


Set SecondRange = Worksheets("Sheet2").Range("A1"). _
Offset(RowOffset:=RowOffset, columnoffset:=0).EntireRow

SecondRange.Select
SecondRange.Insert (xlShiftDown)

End Sub


"MarkHear1" wrote:

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

try this code

Private Sub Worksheet_Change(ByVal Target As Range)


Charcount = 1
If Target.Column < Range("C1").Column Then Exit Sub

If StrComp(StrConv(Target, vbUpperCase), "YES") < 0 Then Exit Sub

Set FirstRange = Target.EntireRow


FirstRange.Select
FirstRange.Cut


Worksheets("Sheet2").Select
RowOffset = 0

Do While StrComp(Worksheets("Sheet2").Range("a1"). _
Offset(RowOffset:=RowOffset, columnoffset:=0), "") < 0

RowOffset = RowOffset + 1
Loop


Set SecondRange = Worksheets("Sheet2").Range("A1"). _
Offset(RowOffset:=RowOffset, columnoffset:=0).EntireRow

SecondRange.Select
SecondRange.Insert (xlShiftDown)

End Sub


"MarkHear1" wrote:

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

Right click on the sheet tab of Sheet1 and select view code. Put in code
like this in the resulting module (for sheet1).

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Column < 3 Then Exit Sub
On Error GoTo ErrHandler
If UCase(Trim(Target.Value)) = "YES" Then
Application.EnableEvents = False
Target.EntireRow.Copy _
Worksheets("Sheet2") _
.Cells(Rows.Count, 3) _
.End(xlUp)(2).EntireRow
Target.EntireRow.Delete
End If

ErrHandler:
Application.EnableEvents = True
End Sub

This is code for the change event. If you are not familiar with events, see
Chip Pearson's page for an overview http://www.cpearson.com/excel/events.htm

Note this will start placing data in row 2 of sheet 2. If you want to start
in row1 it will require some additional code.

--
Regards,
Tom Ogilvy


"MarkHear1" wrote:

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