Hi,

Im reposting because I thought that my intial post ("Transferring data
between 2 workbooks automatically") 3 days back was confusing. Sorry
for that. I have tried to explain my objective in a more concise
manner.

I want to copy data from one worksheet "A" within one workbook "B" to
another Workbook "C" containing a worksheet "A". Worksheet C is
actually a master file and workbook C records daily data and at the
end of the day all the rows in Worksheet A in Workbook B is transfered
to worksheet A of workbook C.

I copied a macro for this :-

Sub Macro5()

Windows("B").Activate
Sheets("A").Select
Range("B3:I35").Select
Selection.Copy
Windows("C").Activate
Sheets("A").Select
Range("B78").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

End Sub


I am facing 2 problems in making this transfer dynamic :-

1. The number of rows in Workbook B and workbook C will be varying. I
want to copy data in Workbook B from the 2nd row to the last row where
data is there. Then I want to paste this data in the last available
row for in workbook C (Workbook C will be already having some data so
I want to paste new data below the last row). Please guide me for
finding the last row for these 2 workbooks.

2. I want to prevent accidental Multi pasting of data from workbook B
in to Workbook C. That is if data has been pasted once by somebody
then I want that somebody else might not paste the same data again.
Basically I want row by row comparison of data in workbook B with
Workbook C and if the data is already there then it is not pasted in
Workbook C but if it is not there then it shouold be pasted. (There is
no unique ID for data comparison and only the whole row in totality
represents a unique record. Hence, 2 records will be same only when
each of the column from B to I is equal for the corresponding
workbooks)

Please help me in resolving the above if possible.

Regards,
Hari
India