Keep last row format/values when transposing values from one worksheet to another

Hi All,

I have a spreadsheet that gets generated from a database.. What i am trying to do is add to my macro a way to paste above value/formats from DESTINATION worksheet..

In my current workbook, it has various columns:

6-18-2012 10-22-09 AM.png

SOME of these columns get transferred to another worksheet for end user input via Macro1.

Code example:

.Cells(18, 4).Value = Sheets("ORIG").Cells(26, 13).Value 'Net Weight
        .Cells(18, 5).Value = Sheets("ORIG").Cells(26, 15).Value 'RN Weight Unit
        .Cells(18, 7).Value = Sheets("ORIG").Cells(26, 12).Value 'Gross Weight
        .Cells(18, 8).Value = Sheets("ORIG").Cells(26, 15).Value 'RN Weight Unit
        .Cells(18, 11).Value = Sheets("ORIG").Cells(26, 6).Value 'X Dim
        .Cells(18, 12).Value = Sheets("ORIG").Cells(26, 9).Value 'Dim Unit
        .Cells(18, 14).Value = Sheets("ORIG").Cells(26, 7).Value 'Y Dim

Then after the data is manually modified (new rows added) on the new worksheet, there is a macro2 that takes those values and pastes them back to the original sheet..

Code Example:

Sheets("ORIG").Cells(nRowDetail + 1, 1).Value = Sheets("NEW").Cells(10, 19).Value   'Packing Number
         Sheets("ORIG").Cells(nRowDetail + 1, 2).Value = Sheets("NEW").Cells(14, 17).Value   'Packing type
         Sheets("ORIG").Cells(nRowDetail + 1, 13).Value = Sheets("NEW").Cells(18, 4).Value   'Net Weight
         Sheets("ORIG").Cells(nRowDetail + 1, 15).Value = Sheets("NEW").Cells(18, 5).Value   'RN Weight Unit
         Sheets("ORIG").Cells(nRowDetail + 1, 12).Value = Sheets("NEW").Cells(18, 7).Value   'Gross Weight
         Sheets("ORIG").Cells(nRowDetail + 1, 15).Value = Sheets("NEW").Cells(18, 8).Value   'RN Weight Unit

The problem I am having is, if the end user adds more rows, those rows do not get the format the rest of the worksheet has, nor does it get any values that i did not transfer with the original macro1.

6-18-2012 10-25-32 AM.png

if you look at the last two lines on the above picture - those are the new lines, that only copy, of course what is on the NEW sheet - I need to grab the colour/formats/values(if blank) from the line above after it is pasted..

I want the macro2 to take the values and paste them, but if the value is blank, then grab the previous value from the sheet it is pasted unto, as well as grabbing the format from the above line on the sheet it is pasted into (text format, interior colour/background colour)..

Does this make sense? Is there any way to do this via my vb code in Macro2?