Macro to copy data from two workbook to another where all workbooks are open

Hi,

I have 3 workbooks open. 1 is my working file and I name it as "Final[date].xlsm". The other 2 files are my source files which are also open are named as source1.xlsx and source2.xlsx. Both the source files has only one tab/sheet as "Sheet1". My objective is to bring the contents of the source file to my working file in 2 different sheets. all the contents in Sheet1 of "Source1.xlsx" should be paste.values only to the "Final[date].xlsm" with a sheet name "BankDetails". This will enable the user also to replace the old data in "BankDetails" tab. The "source2.xlsx" sheet1 has a different situation. I need to copy only the cells with values, not the entire cells, because it has to be pasted (values only) to range C2:L. I have formulas in the other columns before C and after L.

Take note that my working file name is changing every based on the date while my source file has always same file name.

Kindly help.

Hi Guys, I attached sample workbooks. BTW, Source2.xlsx content should go to "Premium" tab.

Hi k64. The problem (I think) on your suggested code is that my source file is not always on the same folder or drive. I cannot change every now and then my code. Is there something that it identifies the workbook that is open with the same file name?

Thanks.

I'm sorry, I am really a beginner in VBA. How can I put that formula to the codes you provided above.

My clarification follow up might not clear enough. What I was trying to say is that, the source file is not always in the C drive. Sometimes, it will be in a server or attachment to an email. The file name is constant though. The user should open the 2 source file and grab the data in it.

I tried pasting the above formula and when I open Source1.xlsx, the cell A1 is updated. That means, I need to put this formula to all the cells in the sheet. I am not comfortable on this because if the user replaces the previous source file, data innthe current workbook will change. I need the data to be fixed in the destination tabs. something that should copy and paste values only.

with regards to the manner on how the source files are opened, I can asked the user to open the source file one at a time, if this can help? Because I cannot guarantee the sequence in opening the file or opening the file on the same order.

Appreciate your help further.

Thanks.

Whew! I love it! it works well... Thanks a lot.