Merging multiple workbooks

Hi Everyone

I have 7 workbooks (single tab) that I need to merge into the master workbook. I have 'put together' a macro that opens the files and moves them to into the master.

As this may be performed repeatedly (the master is split and merged at intervals) is there a way to then delete the files once they have been moved. It just means that when the splitting occurs I get the message 'replace existing file?' which it would be good if I could skip.

The other query was if there was a way to automatically select the seven files (same path as active workbook) and move them without the dialogue box (didn't think you could use an array with workbooks, just sheets?).

I know, there is lots of code on this forum but as I'm a newbie find it too difficult to read and amend. Hence the simplicity of my code (except the bottom bit which I got help with)!!

Would be greatful for any help

Please Login or Register  to view this content.

Please Login or Register  to view this content.

NB. you have to complete choose(j,"Resources","Comm","Strategy") to 7 arguments.
change C:\ into the directory where the files are located.

Hi snb,

Thanks for your help!

I've amended the code and tried to add in the path (copied from another bit of code), the path will change each month hence the below but think I may have the syntax wrong? Or is it excel issue because as soon as I copied the code in it turned red?

Please Login or Register  to view this content.

No need for variables.

Please Login or Register  to view this content.

Hi snb

I've tried running it I get a run time error at line

Please Login or Register  to view this content.

but it does move the worksheet. Do I need to repeat the 'move' line for each of the worksheets?

Thanks

Please Login or Register  to view this content.

Hi,

Sorry to be a pain! I'm still getting a runtime error

Please Login or Register  to view this content.

Thanks for your help so far, but I've tried running and it executes perfectly but it just adds new sheets. I thought is was possible to move sheets between books but I think it may go back to my original query of whether it's possible to not have to click the files to be opened (i.e. dialogue box) as the filenames are static.

From other posts I've read and other sites I thought it was possible but I haven't got a hope in hell of understanding the code, maybe in a year or two..*frustrated sigh*