Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code...
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code...
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code...
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Modification in the CODE to HIDE rows and columns that start with ZERO (code given) | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming |