Need to Merge Data of Multiples Excel Files Together Using VBA

[vbaxcel]

Hi everyone!

I need to create an Excel macro using VBA which can merge more than one excel files keeping headers, columns and sheets.
Two files are attached as examples which need to be merged into one. Format of both files is same.
Every week I get such new file so I need a Macro to add data of the file to previous ones and hence final file at time of running macro should include data of all files merged together.

I have been able to create following code but unable to proceed further successfully.

Please Login or Register  to view this content.

Any help or guidance will be appreciated in this regard !

[AlphaFrog]

There is a free Excel Add-In that may do what you want.

RDBMerge, Excel Merge Add-in


Here is source code you may find useful.

Merging Data from Multiple Workbooks

[Bernie Deitrick]

Use a macro like this, and choose the new weekly file in the dialog. If you need to select more than one, the code can loop through all the files that you select in the dialog.

Please Login or Register  to view this content.

[vbaxcel]

Hi AlphaFrog,
Thank you. These are all really helpful links.

Hi Bernie,
Thank you so much. Your code surely fulfills purpose of merging files but for first sheet only, if I am right?. How this code can be modified if all sheets are needed to be merged respectively?

[Bernie Deitrick]

Please Login or Register  to view this content.

[vbaxcel]

Hi Bernie,
Thank you. Your code merges data of all sheets of a single file into one sheet. However my requirement is to merge data of each similar named sheet of each file.
For example, data in each 'Products' named sheet of both previously attached files/workbooks need to be merged into one and so on with each sheet.
Excuse me if I wasn't clear in my previous post.

[Bernie Deitrick]

Simple change - as long as the sheets already exist:

Please Login or Register  to view this content.

[vbaxcel]

Hi Bernie,

Your updated code works fine as far as merging data is concerned but somehow I am unable to select more than one file in the dialog box. Can you please check again?

Thanks!

[Bernie Deitrick]

It wasn't written to open more than one because you wanted to merge two files into one, which only requires opening one. This version will process as many files as you choose:

Please Login or Register  to view this content.

[vbaxcel]

Thank you so much Bernie. This worked like a charm!

[vbaxcel]

Hi Bernie,

In the same file, now I need to create different files (one per category ID) containing all details from main file. As you can see, main file has many rows and many tabs (containing different category ID). The macro when run should return many files (one per category ID) containing rows and tab, for example:

Category ID 256 one file with all related products and tabs
Category ID 45 one file with all related products and tabs
Categori ID ...........

Looking forward for quick response. Thanks!

[vbaxcel]

Any response would be appreciated please!
Thank you!

[Bernie Deitrick]

I did not download your file, nor will I. My code works with any structure.

To export to separate files based on a specific value in a field, use code like this (see the comments to modify to your needs):

Please Login or Register  to view this content.