VBA to add numbers in columns from various workbooks

Mark from South Africa. Trust you and the family are keeping safe during the COVID-19 situation.

I have 1 master workbook, namely: “Example_FC 2020_Master” with 1 tab called “Master” and then I have 2 excl workbooks, namely:
“Example_FC 2020_Retail_Mark” and “Example_FC 2020_Retail_Peter”, each with a master tab and then various customer name tabs.

The worksheets consist of products.


The idea is to forecast per customer, per product. In the master tab of “Example_FC 2020_Retail_Mark” and “Example_FC 2020_Retail_Peter”, I have used vlookup (very basic), to add all the columns from the various tabs in the same workbook, per product to give a grant total per month (all customers forecast quantities added together per product).
I am sure it is earier to use VBA, than vlookup, but thats for another day.

What I need is the following.....
I would like to have a button in the “Example_FC 2020_Master” workbook, that I can press and what it will do is:
Look (in a specific folder say on my desktop for example), at the 2 excl workbooks namely: “Example_FC 2020_Retail_Mark” and “Example_FC 2020_Retail_Peter”, in their master tabs and extract the quantities, per product and add them together in the “Example_2020_Master” workbook.

I will eventually have about 10 different workbooks with about 300 products, all exactly the same as “Example_FC 2020_Retail_Mark” and “Example_FC 2020_Retail_Peter” all consolidating to the “Example_FC 2020_Master”

Can someone help me with the code if possible? Example attached. Can someone help me with the code if possible? Example attached - Willing to donate to a charity for assistance.

"I have posted on 2 other forums"

It seems you have a fairly recent version of Excel. This sort of thing would be perfect for Power Query, are you against such a solution? If not, which version of Excel are you using?

Good Day p45cal. Thank you for your reply. I wouldn't be against Power Query. The problem is, the company I work for won't buy it for me. We use Office 365.

Quote:

---

Originally Posted by NewbieMark76

Good Day p45cal. Thank you for your reply. I wouldn't be against Power Query. The problem is, the company I work for won't buy it for me. We use Office 365.

---

Then you've very probably got it since it's built in. You should have a Get & Transform data section on the Data tab of the ribbon?

Could you provide links to all your cross posts? - it's netiquette, and a requirement here (and most other forums).

Oops:

https://www.excelforum.com/excel-pro...workbooks.html

https://www.ozgrid.com/forum/index.p...51#post1234153

Quote:

---

Originally Posted by p45cal

Then you've very probably got it since it's built in. You should have a Get & Transform data section on the Data tab of the ribbon?

---

Do you have it?

I cant find it. I checked add-in, ribbons, etc. No power query listed anywhere

I don't think it is called Power Query in office 365, it's Get and Transform Data on the Data tab. I'm 95% sure it's included in Office 365 (perhaps it needs to be enabled or that part of the ribbon made visible - do some internet searching).

https://support.microsoft.com/en-us/...rs=en-us&ad=us
Attachment 26432
It's a lot less work than writing vba code (macros).

Try over here

Attachment 26433

Thank you p45Cal and Paul. Found it. Your assistance is much appreciated. Now to learn power query :-)

Have a great weekend!

In the attached is not a solution but a taster, this is because I'm still learning Power Query.
The pivot table at cell J17 of the attached is the result of Power Query. It's gratifying to see that the results exactly match your desired results.
You won't be able to update (refresh) the pivot table because my source files are not in the same place as they are on your computer/system but I'll show you how to remedy that:
Navigate to where you can get the Queries & Connections pane showing at the right of your Excel window:
Attachment 26442

Then double click on the vbaExpress67219 query, and in the window which pops up, click on the little gear wheel to the right of the Source step at the top of the Applied Steps list in the Query Settings panel (arrowed 1 in the pic) to get this:
Attachment 26443
Click on the Browse… button and navigate to the folder where you've put your files, and click OK.
Then you need to click on Close and Load at the extreme left of the Home tab of the ribbon to close that window. At the moment it's probably safer to have those files by themselves in a folder; I have put something in the query to restrict the files it looks at, but it's rudimentary.

Do this first with the source files you attached to your posts here to make sure it refreshes properly.
Once you've got no errors showing, try changing some data in those files (it looks at the individual customer sheets in those files, and deliberately ignores the master sheets in them)
Currently it's very rigid in what it expects to see in those files (it won't like anything but two columns and expects to see April 2020 and May 2020 as headers). However, you should be able to add rows to that data, with new Materials and Material Descriptions with numbers and they should appear in the refreshed pivot table, but be aware that the query acts on the files in the folder so you need to save the files once you've made alterations before refreshing the pivot (I don't think you need to close the files).

I repeat it's a taster and it has its faults: the dates it produces are based on the headers of the first file/sheet it comes across and assumes the rest are the same! The header for the first column is wrong, again it's (currently 'Mark') from the first file name that it looks at. I'll put these right later - just trying to get robust solutions before I do.

Incidentally, in the Power Query editor, when you first opened it, you should have been able to see the table that's produced by the query, which forms the basis on which the pivot table is made - you won't see that table anywhere else - it's only held in memory (you can change that).

I also attach, for interest, the table that's produced in the background by the query in the Table.xlsx file. You'll see column A contains the name gleaned from the file name, column D contains the sheet name. It wouldn't be difficult to extract Retail/Trade from the file name too.

This is an interesting exercise for me and helps me learn, so will continue to try and improve it and make it more robust and flexible - but I'm hardly the person to give you the best advice, so, and I hope I'm not breaking forum rules here, I suggest you post your query here:
https://www.excelguru.ca/forums/foru...rm)-amp-M-code
It's a site owned by Ken Puls, author of M is for (Data) Monkey, which is a Power Query primer. That forum linked-to above is dedicated to Power Query and it's where you'd stand a very good chance of getting some really good advice and some very slick/elegant solutions.

Good Morning p45cal

I am so very grateful for your assistance. Thank you so so much - will keep you posted on the progress

Mark