Copy and Insert Row - microsoft.public.excel.programming

Hi, The excel vba code does not generate the correct result and incomplete as I've no idea on how to rectify the codes to achieve the intended results Below is the extract of vba codes : - =A0 Dim C As Range =A0 Dim X As Long =A0 Dim LastRowX As Long =A0 Dim LastRowY As Long =A0 Dim CellsToColor() As String =A0 LastRowX =3D Worksheets("Wrksheet X").Cells(Rows.Count, "A").End (xlUp).Row =A0 LastRowY =3D Worksheets("Wrksheet Y").Cells(Rows.Count, "A").End (xlUp).Row =A0 With Worksheets("Wrksheet X") =A0 =A0 ReDim CellsToColor(1 To LastRowX) =A0 =A0 For Each C In .Range("A1:A" & LastRowX) =A0 =A0 =A0 If Worksheets("Wrksheet Y").Range("A:A").Find(What:=3DC.Value, = _ =A0 =A0 =A0 =A0 =A0 LookAt:=3DxlWhole) Is Nothing Then CellsToColor(C.Row) = =3D C.Address =A0 =A0 Next =A0 =A0 .Range("A1:A" & LastRowX).Copy Worksheets("Wrksheet Y").Range ("A1") =A0 =A0 For X =3D 1 To LastRowX =A0 =A0 =A0 If Len(CellsToColor(X)) > 0 Then =A0 =A0 =A0 =A0 .Range(CellsToColor(X)).Cells.Font.Color =3D vbRed =A0 =A0 =A0 =A0 .Range(CellsToColor(X)).Cells.Font.Bold =3D True =A0 =A0 =A0 End If =A0 =A0 Next =A0 End With The intended result should copy and paste each row from sheet1 to sheet2 when the ID number is searched and found in column A of sheet2, then highlight changes in red colour E.g. Sheet1 Column A ID No W070124 G081034 C020998 A107390 Sheet2 Column A ID No B090146 A107390 F002955 W070124 Result Column A ID No B090146 A107390 F002955 W070124 Appreciate any helps on the above problem as I'm excel vba beginner Thanks in advance Regards Len

On Jan 19, 10:17=A0pm, Len <ltong2000...@yahoo.co.uk> wrote: > Hi, > > The excel vba code does not generate the correct result and incomplete > as I've no idea on how to rectify the codes to achieve the intended > results > > Below is the extract of vba codes : - > > =A0 Dim C As Range > =A0 Dim X As Long > =A0 Dim LastRowX As Long > =A0 Dim LastRowY As Long > =A0 Dim CellsToColor() As String > =A0 LastRowX =3D Worksheets("Wrksheet X").Cells(Rows.Count, "A").End > (xlUp).Row > =A0 LastRowY =3D Worksheets("Wrksheet Y").Cells(Rows.Count, "A").End > (xlUp).Row > =A0 With Worksheets("Wrksheet X") > =A0 =A0 ReDim CellsToColor(1 To LastRowX) > =A0 =A0 For Each C In .Range("A1:A" & LastRowX) > =A0 =A0 =A0 If Worksheets("Wrksheet Y").Range("A:A").Find(What:=3DC.Value= , _ > =A0 =A0 =A0 =A0 =A0 LookAt:=3DxlWhole) Is Nothing Then CellsToColor(C.Row= ) =3D > C.Address > =A0 =A0 Next > =A0 =A0 .Range("A1:A" & LastRowX).Copy Worksheets("Wrksheet Y").Range > ("A1") > =A0 =A0 For X =3D 1 To LastRowX > =A0 =A0 =A0 If Len(CellsToColor(X)) > 0 Then > =A0 =A0 =A0 =A0 .Range(CellsToColor(X)).Cells.Font.Color =3D vbRed > =A0 =A0 =A0 =A0 .Range(CellsToColor(X)).Cells.Font.Bold =3D True > =A0 =A0 =A0 End If > =A0 =A0 Next > =A0 End With > > The intended result should copy and paste each row from sheet1 to > sheet2 > when the ID number is searched and found in column A of sheet2, then > highlight > changes in red colour > > E.g. > Sheet1 > Column A > ID No > W070124 > G081034 > C020998 > A107390 > > Sheet2 > Column A > ID No > B090146 > A107390 > F002955 > W070124 > > Result > Column A > ID No > B090146 > A107390 > F002955 > W070124 > > Appreciate any helps on the above problem as I'm excel vba beginner > > Thanks in advance > > Regards > Len Sorry.......... There was an error in the example given earlier and the correct example with result should be : - E.g. Sheet1 Column A B C D E ID No Date Intake Name Amount A107390 27/1/2009 KWDU-03 Mr Lim 7600 C020998 23/1/2009 2070-04 Ms Lin 1450 G081034 22/1/2009 WCDU-04 Mr Tan 200 W070124 22/1/2009 KWDU-01 Mr XY 8500 Sheet2 Column A ID No A107390 B090146 F002955 W070124 Result Column A B C D E ID No A107390 27/1/2009 KWDU-03 Ms Lin 7600 B090146 C020998 23/1/2009 2070-04 Mr Lim 1450 F002955 G081034 22/1/2009 WCDU-04 Mr XY 200 W070124 22/1/2009 KWDU-01 Mr Tan 8500 Regards Len

If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Len" <ltong2000mal@yahoo.co.uk> wrote in message news:5861abc5-8dbc-4e82-b265-5713272b70e8@m26g2000yqb.googlegroups.com... On Jan 19, 10:17 pm, Len <ltong2000...@yahoo.co.uk> wrote: > Hi, > > The excel vba code does not generate the correct result and incomplete > as I've no idea on how to rectify the codes to achieve the intended > results > > Below is the extract of vba codes : - > > Dim C As Range > Dim X As Long > Dim LastRowX As Long > Dim LastRowY As Long > Dim CellsToColor() As String > LastRowX = Worksheets("Wrksheet X").Cells(Rows.Count, "A").End > (xlUp).Row > LastRowY = Worksheets("Wrksheet Y").Cells(Rows.Count, "A").End > (xlUp).Row > With Worksheets("Wrksheet X") > ReDim CellsToColor(1 To LastRowX) > For Each C In .Range("A1:A" & LastRowX) > If Worksheets("Wrksheet Y").Range("A:A").Find(What:=C.Value, _ > LookAt:=xlWhole) Is Nothing Then CellsToColor(C.Row) = > C.Address > Next > .Range("A1:A" & LastRowX).Copy Worksheets("Wrksheet Y").Range > ("A1") > For X = 1 To LastRowX > If Len(CellsToColor(X)) > 0 Then > .Range(CellsToColor(X)).Cells.Font.Color = vbRed > .Range(CellsToColor(X)).Cells.Font.Bold = True > End If > Next > End With > > The intended result should copy and paste each row from sheet1 to > sheet2 > when the ID number is searched and found in column A of sheet2, then > highlight > changes in red colour > > E.g. > Sheet1 > Column A > ID No > W070124 > G081034 > C020998 > A107390 > > Sheet2 > Column A > ID No > B090146 > A107390 > F002955 > W070124 > > Result > Column A > ID No > B090146 > A107390 > F002955 > W070124 > > Appreciate any helps on the above problem as I'm excel vba beginner > > Thanks in advance > > Regards > Len Sorry.......... There was an error in the example given earlier and the correct example with result should be : - E.g. Sheet1 Column A B C D E ID No Date Intake Name Amount A107390 27/1/2009 KWDU-03 Mr Lim 7600 C020998 23/1/2009 2070-04 Ms Lin 1450 G081034 22/1/2009 WCDU-04 Mr Tan 200 W070124 22/1/2009 KWDU-01 Mr XY 8500 Sheet2 Column A ID No A107390 B090146 F002955 W070124 Result Column A B C D E ID No A107390 27/1/2009 KWDU-03 Ms Lin 7600 B090146 C020998 23/1/2009 2070-04 Mr Lim 1450 F002955 G081034 22/1/2009 WCDU-04 Mr XY 200 W070124 22/1/2009 KWDU-01 Mr Tan 8500 Regards Len

On Jan 19, 11:24=A0pm, "Don Guillett" <dguille...@austin.rr.com> wrote: > =A0 =A0 =A0 If desired, send your file to my address below. I will only l= ook if: > =A0 =A0 =A0 1. You send a copy of this message on an inserted sheet > =A0 =A0 =A0 2. You give me the newsgroup and the subject line > =A0 =A0 =A0 3. You send a clear explanation of what you want > =A0 =A0 =A0 4. You send before/after examples and expected results. > > -- > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguill...@gmail.com"Len" <ltong2000...@yahoo.co.uk> wrote in message > > news:5861abc5-8dbc-4e82-b265-5713272b70e8@m26g2000yqb.googlegroups.com... > On Jan 19, 10:17 pm, Len <ltong2000...@yahoo.co.uk> wrote: > > > > > Hi, > > > The excel vba code does not generate the correct result and incomplete > > as I've no idea on how to rectify the codes to achieve the intended > > results > > > Below is the extract of vba codes : - > > > Dim C As Range > > Dim X As Long > > Dim LastRowX As Long > > Dim LastRowY As Long > > Dim CellsToColor() As String > > LastRowX =3D Worksheets("Wrksheet X").Cells(Rows.Count, "A").End > > (xlUp).Row > > LastRowY =3D Worksheets("Wrksheet Y").Cells(Rows.Count, "A").End > > (xlUp).Row > > With Worksheets("Wrksheet X") > > ReDim CellsToColor(1 To LastRowX) > > For Each C In .Range("A1:A" & LastRowX) > > If Worksheets("Wrksheet Y").Range("A:A").Find(What:=3DC.Value, _ > > LookAt:=3DxlWhole) Is Nothing Then CellsToColor(C.Row) =3D > > C.Address > > Next > > .Range("A1:A" & LastRowX).Copy Worksheets("Wrksheet Y").Range > > ("A1") > > For X =3D 1 To LastRowX > > If Len(CellsToColor(X)) > 0 Then > > .Range(CellsToColor(X)).Cells.Font.Color =3D vbRed > > .Range(CellsToColor(X)).Cells.Font.Bold =3D True > > End If > > Next > > End With > > > The intended result should copy and paste each row from sheet1 to > > sheet2 > > when the ID number is searched and found in column A of sheet2, then > > highlight > > changes in red colour > > > E.g. > > Sheet1 > > Column A > > ID No > > W070124 > > G081034 > > C020998 > > A107390 > > > Sheet2 > > Column A > > ID No > > B090146 > > A107390 > > F002955 > > W070124 > > > Result > > Column A > > ID No > > B090146 > > A107390 > > F002955 > > W070124 > > > Appreciate any helps on the above problem as I'm excel vba beginner > > > Thanks in advance > > > Regards > > Len > > Sorry.......... > > There was an error in the example given earlier and the correct > example with result should be : - > > E.g. > Sheet1 > Column A B C D E > ID No Date Intake Name Amount > A107390 27/1/2009 KWDU-03 Mr Lim 7600 > C020998 23/1/2009 2070-04 Ms Lin 1450 > G081034 22/1/2009 WCDU-04 Mr Tan 200 > W070124 22/1/2009 KWDU-01 Mr XY 8500 > > Sheet2 > Column A > ID No > A107390 > B090146 > F002955 > W070124 > > Result > Column A B C D E > ID No > A107390 27/1/2009 KWDU-03 Ms Lin 7600 > B090146 > C020998 23/1/2009 2070-04 Mr Lim 1450 > F002955 > G081034 22/1/2009 WCDU-04 Mr XY 200 > W070124 22/1/2009 KWDU-01 Mr Tan 8500 > > Regards > Len Hi Don, Thanks for your reply and I repost the thread to you together with the attached file for your reference Regards Len

Didn't see it... -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Len" <ltong2000mal@yahoo.co.uk> wrote in message news:25370dfb-4311-4b22-91cb-ecad062a487c@l30g2000yqb.googlegroups.com... On Jan 19, 11:24 pm, "Don Guillett" <dguille...@austin.rr.com> wrote: > If desired, send your file to my address below. I will only look if: > 1. You send a copy of this message on an inserted sheet > 2. You give me the newsgroup and the subject line > 3. You send a clear explanation of what you want > 4. You send before/after examples and expected results. > > -- > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguill...@gmail.com"Len" <ltong2000...@yahoo.co.uk> wrote in message > > news:5861abc5-8dbc-4e82-b265-5713272b70e8@m26g2000yqb.googlegroups.com... > On Jan 19, 10:17 pm, Len <ltong2000...@yahoo.co.uk> wrote: > > > > > Hi, > > > The excel vba code does not generate the correct result and incomplete > > as I've no idea on how to rectify the codes to achieve the intended > > results > > > Below is the extract of vba codes : - > > > Dim C As Range > > Dim X As Long > > Dim LastRowX As Long > > Dim LastRowY As Long > > Dim CellsToColor() As String > > LastRowX = Worksheets("Wrksheet X").Cells(Rows.Count, "A").End > > (xlUp).Row > > LastRowY = Worksheets("Wrksheet Y").Cells(Rows.Count, "A").End > > (xlUp).Row > > With Worksheets("Wrksheet X") > > ReDim CellsToColor(1 To LastRowX) > > For Each C In .Range("A1:A" & LastRowX) > > If Worksheets("Wrksheet Y").Range("A:A").Find(What:=C.Value, _ > > LookAt:=xlWhole) Is Nothing Then CellsToColor(C.Row) = > > C.Address > > Next > > .Range("A1:A" & LastRowX).Copy Worksheets("Wrksheet Y").Range > > ("A1") > > For X = 1 To LastRowX > > If Len(CellsToColor(X)) > 0 Then > > .Range(CellsToColor(X)).Cells.Font.Color = vbRed > > .Range(CellsToColor(X)).Cells.Font.Bold = True > > End If > > Next > > End With > > > The intended result should copy and paste each row from sheet1 to > > sheet2 > > when the ID number is searched and found in column A of sheet2, then > > highlight > > changes in red colour > > > E.g. > > Sheet1 > > Column A > > ID No > > W070124 > > G081034 > > C020998 > > A107390 > > > Sheet2 > > Column A > > ID No > > B090146 > > A107390 > > F002955 > > W070124 > > > Result > > Column A > > ID No > > B090146 > > A107390 > > F002955 > > W070124 > > > Appreciate any helps on the above problem as I'm excel vba beginner > > > Thanks in advance > > > Regards > > Len > > Sorry.......... > > There was an error in the example given earlier and the correct > example with result should be : - > > E.g. > Sheet1 > Column A B C D E > ID No Date Intake Name Amount > A107390 27/1/2009 KWDU-03 Mr Lim 7600 > C020998 23/1/2009 2070-04 Ms Lin 1450 > G081034 22/1/2009 WCDU-04 Mr Tan 200 > W070124 22/1/2009 KWDU-01 Mr XY 8500 > > Sheet2 > Column A > ID No > A107390 > B090146 > F002955 > W070124 > > Result > Column A B C D E > ID No > A107390 27/1/2009 KWDU-03 Ms Lin 7600 > B090146 > C020998 23/1/2009 2070-04 Mr Lim 1450 > F002955 > G081034 22/1/2009 WCDU-04 Mr XY 200 > W070124 22/1/2009 KWDU-01 Mr Tan 8500 > > Regards > Len Hi Don, Thanks for your reply and I repost the thread to you together with the attached file for your reference Regards Len

Similiar Articles:

Copy and insert multipel rows after each row of data - microsoft ...
I need to copy a row and insert it 19 times in the rows below it, for 100+ rows. ...

insert new row and copy formula - microsoft.public.excel.worksheet ...
Hi all, I have a problem with a macro to add a new row and copy formulas. I use the same macro but in different workbooks, the result on formulas ...

Easiest way to insert multiple copied rows? - microsoft.public.mac ...
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Currently, I copy a row, right click and choose insert copied cells...

Script to insert Row - microsoft.public.excel.misc
How would I write a scrtip to insert a row under the number in A that holds a "NS" or "MT"? I would also like it to copy the row that has "NS" or "...

Insert Row without changing formulas - microsoft.public.excel ...
Insert Row without changing formulas - microsoft.public.excel ... Excel :: Insert Rows And Copy Formula Cells Insert Rows/Columns Without Formula References Changing; Copy ...

How to insert a row in a table in Excel? - microsoft.public.word ...
The option to delete the row is available, but not to insert, and a copy/paste only overwrites the row and does not insert either. Please advise.

Macro Help/Duplicate Items + Insert Rows + Sum - microsoft.public ...
In Column X, I have this formula " =SUM(Q4:W4 ... Thanks again for the help, but this one has been ... How to Insert a New Row & Copy ... macro will insert a row and add ...

Stop linked cells from changing when insert new row? - microsoft ...
difficult to explain but here goes. I have a master workbook and have linked (copy>paste special>paste link) cells from it to create several worksh...

Automatically copy formula in column when adding new row ...
"Nadine" wrote: > Have you tried to Copy a row and then Insert Copied Cells instead of > inserting a blank row? > > "Jasmine R" wrote: > > > Hello all.

how do I copy specific rows without copying each row individually ...
Each row ... once I copy the formula. Is there a way to do what I want? I tried copying ... Insert Rows/Columns Without Formula ... How to Insert a New Row & Copy ...

How to Insert a New Row & Copy Formulas in Excel | eHow.com
Microsoft Excel is a spreadsheet application. It allows you to input data and apply formulas. You can format your spreadsheet by inserting one or more cells in a ...

How can I insert an Excel row & copy the FORMULAS too? - Yahoo ...
Best Answer: The following macro will insert a row and add the formula contained in the AB cell above the new row cell. Open your workbook. Copy this macro ...

Excel - Insert Row And Copy Formula - Hi How could... - Free Excel ...
Insert Row And Copy Formula - Hi How could I write a worksheet event code that... - Free Excel Help

Copy and insert Row
Hello. I want to copy a row and then insert it after, within SpreadSheetML using Open XML SDK 2.0 API · After removing all Formulas and updating ...

Excel - Insert Row + Copy Down Format Automatically - how do i ...
Insert Row + Copy Down Format Automatically - how do i insert a new row at the end of my table and automatically... - Free Excel Help

7/8/2012 8:16:19 PM