Macro - Consolidate Column B from multiple workbooks

[souza107]

I am in charge of Consolidating multiple workbooks into one master workbook and send it out.

In the workbooks there are 3 sheets. Each sheet only consist of 2 columns. Column A is the description of Product. Column B has the store name and then Quantity of products. 14 people all fill out this workbook and send it to me. Right now I am copy/Pasting each store from column b into a master workbook. Each store just goes into the next column and the workbook ends up having Columns A-O. Column A still has description of product and columns B-O has each stores info. I am doing this for all 3 sheets in the workbooks. I know there has to be a way to make a MACRO to do this but I am having some trouble figuring it out. Can anyone please help?

[Sisyphus]

Hi,
This sounds like all workbooks have the same list of items, but with different quantities in column B. If this is so you wouldn't need code. Try this way:-

1. Copy all your 14 worksheets into one workbook.
2. Add a 15th worksheet on top as a master. Let it be blank but otherwise formatted like the others.
3. In column A copy the item name from any of the other worksheets. Use a formula like this:

Code: Select all

='Sheet1!A1


4. Int the B column make a total of all sheets. The formula should look like this one:

Code: Select all

='First Sheet:Last Sheet'!B1


5. Use Copy / Paste Special (Values) to change your formulas to hard text and numbers.
6. Delete the 14 sheets
7. Done

Repeat the procedure for each of the 3 worksheets in each workbook.
Let me know if your problem can be solved in this way.

[souza107]

Thanks for the response.

This does not seem much easier than just copying and pasting the information each week into the master work sheet.

I have seen online that people have made macros that can just have all the workbooks in 1 folder and then a macro would compile them all into 1 master work sheet. Even if I had to open the file is there a macro that I could run that would just take column B from a worksheet and paste it the first available empty column in the master workbook?

I have attached 3 files. The first 3 are examples of what my team sends me and the master workbook is an example of what the compiled workbook looks like. Thanks again for any help.

[Sisyphus]

Hi,
You have just shifted the goal posts!
How many worksheets are there in each Workbook? Do all the workbooks have the same number of sheets? Are there likely to be differences in naming the sheets, such as one store reporting on "TV", the next on "TVs" and all the others on "Televisions"?

[souza107]

Hi,
Every Workbook may have a different name but the workbooks themselves will all have the same 3 worksheets with the same names as I am the one that made the template. I send the workbook out, each member fills out the quantity of stock in column B on each sheet then they send it back to me. I then compile a master workbook with everyones information and send it to my boss.

[Sisyphus]

Hi,
OK, I'll look into this. Perhaps this evening or tomorrow.

[souza107]

Thank you for your time.

[Sisyphus]

Hi,
Ok, the attached code will probably do what you want, but it is a bit of a project and, therefore, needs a little attention from your side, too. First of all, here is the system:

The code is contained in the attached workbook which is blank except for the items list.
You save all the files from your stores into one folder. The code will transfer one column from each worksheet into the next free column in each of the Master's worksheets. It will add a line total and, for good measure though useless, a grand total. If your sheet wasn't blank existing columns will NOT be over-written. Instead, extra columns will be added on the right. You can add as many items and as many worksheets as you want. But if there are more worksheets in a workbook coming from a store the extra sheets will be ignored. There is no check which sheet from the store is posted to which sheet in the master. The code just takes the first three of each in the sequence in which they exist in the workbooks. If a store reports with only two sheets the respective column will remain blank but you will not be informed.

All the code is contained in a single module. It is called Module1. You can copy this entire code and paste it into a workbook of xlsx type you have created on your own system. You can also export it using the VBE's export function and import it in your other file. I recommend that you do change the file.

At the top of this module there are a few parameters which you can set at any time. In fact, you must do so before you run the code because the code was developed on Excel 2003 and you are running a more recent version. Here are the parameters, and I will explain their meaning:

Code: Select all

    Const FilePath As String = "D:\My Documents\ExcelKey\Examples\"
    Const FileType As String = "xls"
    Const DataCol As String = "B"
    Const FirstDataRow As Long = 2


The FilePath is the path where your 14 files will be. Your master need not be in the same folder, but if it is there if you wish. Don't mis-spell the path name. Excel wants it to be exact, and will punish you for any inadvertent blank spaces you may have thrown in but doesn't mind your choice of upper or lower case.
The FileType will be "xlsx" in your system. You must change it, unless your stores give you files of another format. With the current configuration of this code you can't look for more than one filetype at a time.
The DataCol is the column where the data are that you wish to import. You told me column B and that is the setting you see above and in the code. If, next month, you want your stores to write their reports in column F, change this parameter to "F" and the data from column F will be imported. The parameter is also used to define the first column in your master sheet. So, if you import data from column F in future columns A to E will not be touched in the Master, either.
The FirstDataRow is the first row in both, the stores' sheets and your master sheet that contains data. If, in future, you need more of a header, you can change this to a larger number. You can't use row 1 for data.

I have envisiged the files you get from the stores to have a date in their name. I thought of a format like this:

YYMMDD STOCK - Store Name. xlsx

The reason for the date is that you may want to be a little selective in which files are picked from the folder and their columns B thrown into your Master. You can use any kind of date. "AUG12" will work as well as "Week 47". In fact, you don't need to use any date at all. Any identifier will do. Now, if you have the files from last week in your folder and those from yesterday, the program can differentiate between them by their name and you can combine those into your master that have a common prefix. All others will be ignored, as will be the Master, even if it has the same prefix. Workbooks are opened Read Only and are closed after they have devulged their secrets.

When you are all set up, select Tools / Macro / Macros from the Master sheet. The macro's name is ImportStockLists. If there are no other files with macros in them open it will be the only one in the list and pre-selected. Make sure it is selected and click the Run button. You will be asked for the Date which I have described above. If you enter nothing or press the exit button at the top right corner the operation will be cancelled. If you enter a "Date" that finds no matching files, you will be told that there are no files to import from. If some of your files don't have the correct date they will be ignored without warning while others are processed.

I suppose that just about covers it. Enjoy all the free time you will have from now on!

[souza107]

Thank you so much. This seems to be exactly what I was looking for. I will try this tomorrow in the AM and let you know how I make out!

Thanks AGAIN!!!

[Sisyphus]

Hi,
I just learned that part of the code I used in your solution can't run on Excel 2007 and later. So, if you don't run Excel 2003 my code won't either. I have already created a solution for the solution and attach a an updated version of the file in case you need it.