I am referencing, to copy the rows from file A.xls to B.xls.
Every week I add new rows to file A.xls which I expect to appear in B.xls
Rather than me having to pull the cell down every week in B.xls to copy these new data of A.xls
Is it possible that rows appear automatically in B.xls when I update the file A.xls.
(Note: I am adding new rows and not updating the present row)
What do you mean by this? Why do you have to pull down the cell every week? Does the cell have formulae that automatically brings in the values from A.xls?Rather than me having to pull the cell down every week in B.xls to copy these new data of A.xls
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Yes, I have referenced a row of B.xls to the row of A.xls
For example, 1st row of A is linked to B by formula. When I add a second row to A it should automatically appear in B. Rather than me having to drag down the row of B to get the new row.
Makes sense ?
You can drag the formula in B.xls for about 1000 rows in advance. So that whenever data is added to A.xls, you do not have to drag them each time a row is added.
By the way, what is the formula that you are using in B.xls?
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
That may not solve my problem because I am referencing different files in single sheet. So linking 1000 rows will disable other linked file. Basically I want the last three latest rows of A to appear in B because that's what I care about. Same for all the linked file. For eg last three rows of file A,C,D,E to appear in File B.
Regarding the formula I just type "=" in cell of file B.xls and clicking on the cell of A.xls I want to copy and excel will auto reference.
Oh ok, got your point. Please upload a sample file with dummy data in the same format as A.xls and B.xls, so we can understand it better.
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
It contains the date like text,numbers and date (Just take random data pls). So we have to find the row containing the last non-zero data and copy it to B.xls from A.xls.
You can use this code. Its not very efficient but works fine. You can use it till we get a better solution
I have considered dummy data in columns A to C.Option Explicit Dim lrow1 As Long Dim lrow2 As Long Dim noofrows As Long Sub compare_files() lrow1 = Workbooks("A.xls").Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row lrow2 = Workbooks("B.xls").Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row If lrow1 <> lrow2 And lrow1 > lrow2 Then noofrows = lrow1 - lrow2 Workbooks("A.xls").Worksheets(1).Range("A" & lrow2 + 1 & ":C" & lrow2 + noofrows).Copy _ Workbooks("B.xls").Worksheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1, 0) End If End Sub
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks