Expert: Bob Umlas - 3/17/2008

Question
QUESTION: There are 2 workbooks (one New and one Master).  Both workbooks have the same column headings (there are 8 columns).  Every time there is a change to data under Column B in the New workbook, I would like a Macro to append the entire row that was changed from New workbook to the Master workbook as a new row and the new row in the Master workbook will be highlighted in Yellow color. In addition, if a new row is inserted (in the middle or anywhere, it could be at the end), I would like the Macro to append the entire row from the New workbook to the Master workbook as a new row and the new row in the Master workbook will be highlighted in Yellow color.



ANSWER: There's one basic problem which needs to be addressed: If an entire row in New is being updated, from col A thru H, then when col B is changed and the macro "kicks in", the rest of the information is not yet present to copy to the Master. Same when a row is inserted -- there's no information present yet -- the user is ABOUT to enter new data in the inserted row. But you're requesting a macro to transfer data when col B is changed, even when most of the data isn't present. Please clarify.

---------- FOLLOW-UP ----------

QUESTION: I apologize for being unclear.  Usually, the New file is sent to me by email.  I only update the master file after the changes/addition on the New file has been saved or completed, which means all columns(A-H) are complete.

Thank you for your help

Answer
Still -- are you only changing column B? If so:
right-click the sheet tab, select View Code, paste this in:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Column = 2 Then
       Cells(Target.Row, 1).Resize(1, 8).Copy
       With Workbooks("Master.xls").Sheets(1).Range("A65536").End(xlUp).Offset(1)
           .PasteSpecial
           .Resize(1, 8).Interior.Color = vbYellow
       End With
   End If
End Sub

Note -- it is assumed that the master.xls is open.