combine files into Master workbook template

**gr8wi9** · 12-07-2012, 11:39 AM

I found a macro that does almost what I want on excelribbon.tips.net

http://excelribbon.tips.net/T010400_..._Workbook.html

I've been using this to combine Excel files into a Master workbook. But I have two other large macros to run on the data in the Master. So I set up a template with the three macros. But when the first macro runs, it creates the Master as a new workbook (ex: Book2). I want the files pulled into the template file so the other two macros are available to run. After the analysis is done I'll save it under a new name. Or is there another way to do this without using the 'personal' method? I want this to be available to other users.

One of the other macros is in my thread "copy data with criteria from large table to new sheet".

I posted my question on the other site, but it doesn't seem to be as active as this one.

Thanks,
Jim

**arlu1201** · 12-07-2012, 02:46 PM

Firstly, you will need to post the code here so we can help you edit it.

Secondly, I'm afraid your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

**gr8wi9** · 12-07-2012, 02:57 PM

Sorry. New user fault. I mixed up forum names, but the link was to the code from that forum. As I mentioned (maybe too gently) the other forum is not very active so I wouldn't expect a reply from it any time soon.
So, I hope I can get help here. Shall I pose my question another way?

**arlu1201** · 12-07-2012, 03:00 PM

Ok, will you be able to post the code here so we can help you quickly? It will also help others searching for a similar solution to find this thread and the code.

**gr8wi9** · 12-07-2012, 03:11 PM

Here's the code. I did refer to this earlier today in my first post to the forum. So maybe I violated the rules on that post also. I thought I had a new topic that was a followon to my first question.
Anyway, the problem I'm having is at

wkbTemp.Sheets(1).Copy
wkbTemp.Close (False)

That is where a new workbook is created. I don't want a new workbook. I want everything to go into the same workbook that holds this and two other macros so I can run them in turn. I'm sure the problem has to do with the workbook names, ActiveWorkbook, or ThisWorkbook but I don't know how to fix it.

Please Login or Register  to view this content.

**arlu1201** · 12-10-2012, 03:13 PM

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.

**gr8wi9** · 12-10-2012, 03:58 PM

Thanks. I'm still stumped on this one. I think the original macro uses wkbAll to collect all the excel files into one workbook, but it always names it Bookn when it gets there. I've tried renaming things to always point to the workbook containing the macros, but I'm not succeeding. I tried recording a macro just copying three files in from another member's suggestion but I can't even figure that out.

**arlu1201** · 12-11-2012, 02:30 PM

Can you please upload a few of your delimited files so i can assist you easily?

**gr8wi9** · 12-11-2012, 02:55 PM

Thanks.
CompiledTest2 has the macro CombineXLSFiles to pull them into a single workbook. The other three are sample data files. I'm trying to get all the files into the CompiledTest2 workbook so all three of the macros you see can be run. After the files are moved into sheets, the copy columns macro will pull some of the columns into the Master sheet.

**gr8wi9** · 12-11-2012, 03:39 PM

Now with the files. Something crashed before.

**gr8wi9** · 12-12-2012, 06:40 PM

Success! I finally got this to work right. Don't ask me how, but I fumbled around long enough to get it to work. This might be useful for others too. As I explained before, one macro combines any number of selected files into this workbook. There is a second macro that copies some columns to a Master sheet. A third macro sifts through the master sheet to find "BadData" based on numbers in the Criterion sheet. I would delete the earlier posts with non-working versions but I don't see how to do that.