Macro to pull workbooks in a folder into a Master workbook

[pmk11]

So I have been googling this for a couple days now. The best thing I could find was a macro on rondebruin.nl, but I need some more specific help.


I have about twenty files in a folder, and will be adding an additional file every month. Each file is very similar, they all have a cover sheet, and then 4-5 worksheets with the data I want. The reason some files have less sheets is they revised the names of the worksheets this year for the monthly data, and added an extra worksheet. So I am dealing with two kinds of files, the first files have worksheets named "Capabilities - Cover" (this one I don't need), "Capabilities (1)" all the way to "Capabilities (4)". The updated files also have "Capabilties - Cover", but then "Capabilities (Light)" "Capabilties (Medium)", with the names in the brackets changing 5 times for a total of 6 worksheets.


Hopefully this isn't too confusing. All the worksheets with the same name from file to file are idemtical. But the range from worksheet to worksheet changes slightly, so the largest range I want is B11:S77.


I have figured out that I need a macro that gets the worksheets with a bracket in them, that weeds out the cover sheet. And I need to pull the range B11:S77 from all the sheets so that I don't miss any data. Plus, the macro must be able to add the additional workbook that I add to the folder every month.


I could really use some help (I am a very novice VBA user)


Thank you,

Mark

 

[SirJB7]

Hi, pmk11!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Give a look to this link, maybe it's what you're looking for, or at least a kick-off point:

http://chandoo.org/forums/topic/macro-to-copy-multiple-workbooks-to-a-master-workbook


Regards!