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.

 

[mumps]

I started playing with your files and I'm sorry to say that I won't be able to help. Macros depend for the most part on consistent patterns. In the Consolidated workbook for example, some of the "IB" sections in row 3 have 8 columns and others have 9 columns. Also, the columns in yellow in the Report workbooks, rows 6 to 17, are not contiguous and the yellow columns in row 24 are also non-contiguous and in different columns from those in rows 6 to 17. You also have many merged cells in your files and these most often create problems for macros and should be avoided at all cost. These are a few examples. It's not that a solution is not possible, it's that there are so many variables that based on my experience, it would make it impractical. Perhaps another Forum member with more experience would be able to find an efficient solution. I don't know. Sorry.

 

[Charles Wanda]

I started playing with your files and I'm sorry to say that I won't be able to help. Macros depend for the most part on consistent patterns. In the Consolidated workbook for example, some of the "IB" sections in row 3 have 8 columns and others have 9 columns. Also, the columns in yellow in the Report workbooks, rows 6 to 17, are not contiguous and the yellow columns in row 24 are also non-contiguous and in different columns from those in rows 6 to 17. You also have many merged cells in your files and these most often create problems for macros and should be avoided at all cost. These are a few examples. It's not that a solution is not possible, it's that there are so many variables that based on my experience, it would make it impractical. Perhaps another Forum member with more experience would be able to find an efficient solution. I don't know. Sorry.

Not a problem. I am grateful for your assistance with the previous files and thanks for your feedback. Much appreciated.

 

[mumps]

My pleasure.