How to merge data from two excel workbooks into a third workbook and update automatically

[bdrod]

Hi All

I am working on creating a database for the quotation team in our company. My project has 2 parts.

PART 1: We have two workbooks currently which record all quotations done for AIR transport and SEA transport. I need to merge the data from these workbooks into a master database which then can be accesses but different users. The master database should update any new quotes in either AIR or SEA automatically.

Attached are the source workbooks - Book2 AIR and Book3 SEA. The data from these has to be combined into Book4 Master.

In the Master workbook, the data from both Air & Sea should merge to appear as shown. When there is a new entry in either AIR or SEA it should appear at the end of the last entry for AIR or SEA. Refer cell highlighted in yellow.

I have tried to do this using the Paste Link function but it makes the file to heavy and does not really update automatically unless tables are extended to include empty cells but then that causes too many blank cells in the master - so not a suitable option. Is there a better solution using code or formulas or macros that can achieve this easily?

PART 2: From the master database created, I need the data to be split into 4 different workbooks based on UK branch (includes data for AIR & SEA) - so one for Heathrow, Purfleet, Tamworth and Manchester so each branch can view only their data and not the entire sheet.

[FDibbins]

Why not just have 1 file with all the data entered onto 1 sheet? Keep it simple

[dflak]

The attached uses static names for the data directory, air file name and sea file name. The macro clears out the old data, opens each file in turn and copies it onto the combined data sheet.

If you want this to be fully automatic, then you can call the routine from an open workbook event. However that won't update the data if it changes after the workbook is open. If this is needed, then let me know. I have some untested ideas on that.

[bdrod]

Hi Dflak

I downloaded your file but I don't quite understand how it works....sorry! It is ok if the data updates when the workbook is closed...I don't specifically need it to do real time updates.

To explain the process and the reason for the database - The quotation team will really be working only in the air file and sea file. So I need the master file to update each day with their individual inputs. The master file is then reviewed by a different team to provide feedback on the quotes.

So as I understand, the master file will update every time the air / sea file is closed?

[JOHN H. DAVIS]

FWIW;

Sheet1 Air:

Please Login or Register  to view this content.

Sheet1 Sea

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Part two:

Please Login or Register  to view this content.

[dflak]

The workbook lets you define a couple of things:
The Control Panel Tab lets you set up a couple of things by changing cell values so you don't have to hard code them in.

Cell B3 is the directory path to where the data files are stored. You will have to fill this in with your own data.
Cell B4 is the name of the file that contains Sea Data - complete with file extension.
Cell B5 is the name of the file that contains Air Data - complete with file extension.

If these files are provided periodically and always have the same name such as Air160601.xlsx you might even be able to use formulas based on the current date to generate those names.

The code itself goes to the specified path and opens first one file and copies it into the book, and then the other file and copies it into the book.

[bdrod]

Thanks dflak...I made the changes and it works fine now. I still need to structure it a bit though...

1. Is it possible to copy the data starting from Row 1, Column E onwards? My original source data starts somewhere around Row 8 but I don't want the combined data to start in Row 8. Also, I need the first 4 columns for status updates and therefore need the data to copy from column E onwards. Is this possible and where in the code can I make the changes to do this?
2. Currently the combine data has 2 header columns, one from each file. I do not want it to copy the header column from the second file....instead I need to have it as one continuous database - so if the data from the Air file ends at row 840...I need the Sea data to copy in row 841. Is that possible?
3. Is there any way to achieve this without having somebody click the Combine Data button? So I guess that means what you said earlier about using a workbook open event? I do not need it to update when the file is open but if I can update every time the file is opened, it would be super

Hope you can help me with these points - appreciate your help so far.

Dear John Davis - thank you for your response, I haven't had a chance to try it out yet but will do so shortly.

[dflak]

I have a couple of questions about your comments.

First to change copy from column A to column E, change the code as follows:

Please Login or Register  to view this content.

And

Please Login or Register  to view this content.

I’m a bit confused about what you mean about the data starting on row 8. That’s not the way the sample files showed it. If this is the case and the header rows are on row 8 but the data itself starts on row 9, then change these lines as follows:

Please Login or Register  to view this content.

And

Please Login or Register  to view this content.

The code is designed to keep the header in the consolidated data and copy from the line below the header from the source file. So there should be no duplication of headers.

To run the code as a workbook open event, Press Alt-F11 and double lick ThisWorkbook. Copy and paste this code in there.

Please Login or Register  to view this content.

[bdrod]

Thanks dflak...appreciate your help...I have managed to change the code to get it the way I need it in the master. However I now have a problem - its not copying out the data from the Air file and I cannot understand why! it probably has something to do with the changes I made in the code but I cannot figure how to resolve it...

I have now updated the Air and Sea file with the format changes - starting from Row 8 etc. so it would help if you could check to see if the code is correct....VB code is not one ofmy strong points...

Thank you.

[dflak]

You almost got the code right. The biggest issues was with the last rows. The formula works by looking at a specific column. So when looking at the source data, use column A, but when looking at the combined data, use column E.

Also, the first copy goes to row 2. There is no need to calculate a last row for it. Once it is copied in, we don't know what the last row will be so we do have to calculate it.

I added some more comments to the code.

[bdrod]

It works perfectly! Thank you very much for your help dflak

[bdrod]

Hello

With reference to my initial post, I am now working on the second part of my project. Combining the data into a master file works perfectly now but I need to work on separating the data based on each branch.

I tried using an INDEX formula but because it is referencing a different workbook, the file takes too long to perform any action. Multiple users will need to use the branch files so it would not work very well if it takes too long to perform any action. Is there any code which will help to separate out the data for each branch into a different sheet automatically?

So for eg. All the data for Purfleet (both Air & Sea) from the combined master file should be displayed in a different workbook Named Purfleet - format is same as the combined master template. I am not sure if I need to create a separate workbook which the code will pick up or will the code create a separate workbook? I would prefer if the code can copy the data into a workbook named Purfleet.

Any ideas on how to proceed?