Vba to consolidate multiple workbooks

[Nevermore158]

Hi i'm new here so i hope i do this right...

I've been looking around for some code to solve my problem and have found similar situations but have been unable to adapt anything to my needs.

What i'm trying to do is create a macro that will loop through a load of excel workbooks with varying file names all stored in the same location.
I need the macro to copy the Range A1:P1 from worksheet 1 in each workbook and add it to the next blank line in a master workbook. Each sheet including the master has identical structure.
Once each workbook has been processed i would ideally like it to be deleted if possible.

Any help would be much appreciated.

 

[AlphaFrog]

Here's a free Excel Add-In that will do you you want..

RDBMerge, Excel Merge Add-in


Here's the VBA code...

Merging Data from Multiple Workbooks into a Summary Workbook

 

[stumac]

Edit: Just seen the above link, this will be far better than my form!!

I would ideally like it to be deleted

I would recommend against doing this via the same macro you are merging with - if there is a problem with the import or it crashes you would lose the source files - even if they are recoverable it is probably more hassle than you would want.

Here is a link to an excel workbook which includes a form I created a few years back for merging files. This includes the ability to rename them after use (it will suffix them with a word) - it also includes the ability to skip files with a certain word in their filename - I usually Suffix with 'Merged' and exclude importing anything with the word merged in the filename that way they are not re-imported.

https://app.box.com/s/skphqmqpizzhhwp4h4l585a3a3tg0o0k


Even if this doesn't do what you need - hopefully its a steer in the right direction.

If you are determined to delete the files after import then use the Kill Command... beware though, the files will be gone - they don't seem to go to the recycle bin.