Write a VBA Code and VLOOKUP Values from multiple excel files into a master sheet

[Charles Wanda]

Hi,

Grateful if anyone can help with this.

I have a set of production data which is reported on a daily basis (Image A: Report For January 1, 2022) i.e different excel Worksheets that represent production data for a specific day(date). There exist a specific folder on my desktop where all the reports are saved.
However, to consolidate these production data for the different days into a single master table/excel in a specific order as seen on Image B (Master Excel Sheet Table)), I have to manually enter the data. I realized this method is prone to mistakes.

Given that the daily reporting format for everyday remains the same, what VBA code can I use to automate the process?


Thanks in advance and I look forward to your responses.

 

[Charles Wanda]

Did the macro work properly for you?

Hi Mumps, I do not know why for some reason I am not able to get it to work properly.

 

[mumps]

How is it not working for you? Does it do anything? Does it open the files? Click here to download your files. I tested the macro using the Jan. 1 - Jan 3 reports and it worked properly. Try these files and see if it works for you.

 

[mumps]

This is what I got with the 3 files:

Master Sheet(Consolidated).xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1		MASTER EXCEL SHEET TABLE
2		CLASS	1A			1B			2A			2B			3A			4A			4B			5A			5B			6A
3		DATE	Production-	Production-	Recycled	Production-	Production-	Recycled	Production-	Production-	Recycled	Production-	Production-	Recycled	Production-	Production-	Recycled	Production-	Production-	Recycled	Production-	Production-	Recycled	Production-	Production-	Recycled	Production-	Production-	Recycled	Production-	Production-	Recycled
4			Gross	Net		Gross	Net		Gross	Net		Gross	Net		Gross	Net		Gross	Net		Gross	Net		Gross	Net		Gross	Net		Gross	Net
5
6		January 1, 2022	100	85	15	150	127.5	22.5	200	170	30	250	212.5	37.5	300	255	45	350	297.5	52.5	400	340	60	450	382.5	67.5	500	425	75	550	467.5	82.5
7		January 2, 2022	50	42.5	7.5	100	85	15	150	127.5	22.5	200	170	30	250	212.5	37.5	300	255	45	350	297.5	52.5	400	340	60	450	382.5	67.5	500	425	75
8		January 3, 2022	78	66.3	11.7	128	108.8	19.2	178	151.3	26.7	228	193.8	34.2	278	236.3	41.7	328	278.8	49.2	378	321.3	56.7	428	363.8	64.2	478	406.3	71.7	528	448.8	79.2
9		January 4, 2022
10		January 5, 2022
11		January 6, 2022
12		January 7, 2022
13		January 8, 2022
14		January 9, 2022
15		January 10, 2022
Sheet1

 

[Charles Wanda]

How is it not working for you? Does it do anything? Does it open the files? Click here to download your files. I tested the macro using the Jan. 1 - Jan 3 reports and it worked properly. Try these files and see if it works for you.

It doesn't open any of the files. I would try this which you've shared and see if it works for me. Thanks

 

[mumps]

Make sure that the folder path in the code is correct and that the files have an ".xlsx" extension.

 

[Charles Wanda]

Make sure that the folder path in the code is correct and that the files have an ".xlsx" extension.

Hi Mumps, I am able to run the VBA code now and it works properly. I had to change the file path to my desktop and it works. I am not so sure why it doesn't work with the other. I am grateful for your assistance. Thanks a lot.

I have a similar problem consolidating another data with more entries than this. I would try applying your code to if it works. Please permit me to say I would revert if I need further assistance with this or anything else.

Thanks once again.

 

[mumps]

You are very welcome.

 

[Charles Wanda]

You are very welcome.

Dear Mumps, Thanks a lot for your assistance with the macro.
I however have something similar to the previous but a little bit more complicated. I tried modifying the macro to work for this but I couldn't.

A description document and other files to what I am trying to achieve is found on the attached link. I would be grateful once again for your assistance. Test_Folder

 

[mumps]

What you are asking is a little more involved. I will be busy for the next few days. I will have a closer look at it when I get some time and see if there is anything I can do.

 

[Charles Wanda]

What you are asking is a little more involved. I will be busy for the next few days. I will have a closer look at it when I get some time and see if there is anything I can do.

Not a problem. Anytime most convenient by you works for me. Thanks a lot.