Transferring data between 2 workbooks automatically
 

Hi,

I want to paste data in to a worksheet called "Raw Data" which is in
Workbook "Raw data in one Worksheet ver 11.55" by copying data from another
Worksheet "Raw Data" which is in another workbook "CCT format ver 2". The
range to be copied is from B3 to Rth column with the last row depending on
the number of rows in Raw Data worksheet.

This data once copied is then pasted at the next to last row in the Workbook
"Raw data in one Worksheet ver 11.55" of worksheet "Raw Data" .

Presently I recorded a macro but wanted my suggestions on making it dynamic.
(I recorded this by keeping the module in the workbook "CCT format ver 2")


Sub Macro5()

Windows("CCT format ver 2.xls").Activate
Sheets("Raw Data").Select
Range("B3:I35").Select
Selection.Copy
Windows("Raw data in one Worksheet ver 11.55.xls").Activate
Sheets("Raw Data").Select
Range("B78").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

End Sub


I have 2 problems here.

-- First the data pasting location in Windows("Raw data in one Worksheet
ver 11.55.xls") is chosen as Range("B78") while recording the macro, but in
actuality the last available row may change.

Its like I have data in the workbook from "Raw data in one Worksheet ver
11.55" columns A to AG and some of those columns may be blank. I want to
determine the last empty row and paste the data in column B of that
row.Hence, the row number 78 has to be made dynamic above. ( Please note its
possible that Column B might not be populated in some rows but atleast one
column thru B to AG will be having data).

Also, I have chosen ("B3:I35") as the range for data copying. The 35th row
has to be made dynamic just like above

-- Second ( Im asking a very luxurious/extravagant stuff here...) I want to
prevent double data updations. That is if the above Macro5 is used once to
update data from the source workbook to the target workbook then it shouldnt
happen that the same data gets uploaded again. I mean if the source data
doesnt change in any of the columns then that duplicate Row doesnt get
uploaded to the target workbook. (If possible a message to the user that
such and such rows in the source data already exist in the target data
workbook)

I want that only those rows to get uploaded in to the target file which are
not already there. ( For me each of the rows in source data is seperate and
similarly each of the rows in Target is seperate. So 2 rows are different if
atleast one of the columns are different - for both within the workbook and
across the workbook).

Please guide me for the same if possible.

Regards,
Hari
India