Excel VBA Match Columns and Paste from One Workbook to Another

  • Try this macro in the Sheet1 code module in DATA FILE A. This macro will only copy over the data changes for existing accounts. New accounts will have to be entered manually in both files. After you enter new accounts, any changes you make to those new accounts will be copied to DATA FILE B.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi, Mumpus,


    You have done great job for me. I applied the code as you said.


    There is a something I want say. When ever I am deleting any row of file A the data in file B exist, do not update. Is there any solution of it?
    Ok there is some formula I am using to collect data from file A to B in appropriate cell.


    {=INDEX('[DATA FILE A.xls]Sheet 1'!$B$2:$B$598,SMALL(IF('[DATA FILE A.xls]Sheet 1'!$D$2:$D$598=L$3,ROW('[DATA FILE A.xls]Sheet 1'!$D$2:$D$598)-ROW(INDEX('[DATA FILE A.xls]Sheet 1'!$D$2:$D$598,1,1))+1),M243))}


    Why the code need to paste in File A instead of file B that's because File B basically the master File and constant. On the Other hand File A is variable.
    It will be great if File B having the code.


    ????


    Thanks Again
    All the best.

  • If you delete a row in DATA FILE A, do you want to delete the corresponding row in DATA FILE B? When you say "It will be great if File B having the code." do you want the 'Update Data' buttons back in DATA FILE B?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps,


    No, Not like that. If I delete a row in data file A then Data file B update automatically. Suppose I deleted row 1 in data file A now row 2 data in position of row 1 data. In data file B update automatically, where row 1 showing row 2 data of File A which is now in row 1 positions. Update button is ok but if update button click twice data just update, if same nothing happen. Then there may be need of collect data button. If so collect data button do not create duplicate entry.


    Thank you.
    All the best.

  • I don't quite follow. I understand that if you delete row 2 (Account Number 270000001 - TBS) in File A, Account Number 270000002 - TBS account will move up to row 2. In File B in the TBS sheet, Account Number 270000001 is currently in row 2. You don't want this account also to be deleted. Is this correct? Do you now want row 2 in the TBS sheet to show Account Number 270000002 from File A? If so, do you want Account Number 270000001 in TBS sheet to be moved down? Another problem is that the columns in File A don't match the columns in File B so which columns do you want copied over? Also, I don't think that you need any buttons in File B because all the updating will be done automatically. If you want a 'Collect Data' button, you will have to explain again which columns you want to copy over. What happens if you delete row 10 in File A? Please use specific examples of account numbers to explain and refer to the row numbers and column letters.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps,


    Sorry for unable to explain to you correctly.


    In File A, TBS Account number 270000001 row No=2 if deleted then what is the first Account number in TBS criteria defiantly Account number 270000002. Then what account number will be displayed in Sheet TBS, File B, defiantly Account number 270000002. This is what I want that File B Update automatically.


    In the previous code When I delete 1st row of File A to which have the data of TBS reference account number 270000001 then I go to File B, Sheet TBS see that account number 270000001 existing there. Why account number 270000001 is present in file B now ?


    That is the problem I am facing.


    Sorry Again If I am unable to explain correctly.


    Thank & All the Best.

  • I think that the best way for me to understand exactly what you want to do is for me to see a 'Before" and 'After' view of your data. Do the following:


    1. Post the latest copy of your actual File B. This will give me a "Before" view of the data.
    2. Post a second copy of File B to show what sheet TBS would look like after you delete Account number 270000001 row No=2 in File A. This will give me an "After" view of the data so I can see the difference.


    The other question I have is: Will you always delete only row 2 of File A or will you delete any other row, for example row 10 of File A? If it is possible that will delete any row other than row 2 in File A, then attach a third copy of File B that would show what sheet TBS would look like after you delete row 10 - Account Number 300000008.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi, Mumps


    Thanks for the support and Sorry for the late. I having some problem so I am using your first code that you provide without making it more complex for me.


    Thanks Again.
    All the Best.

  • You are very welcome. :)

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!