Hello everyone,
I am intermediate user of excel with no coding background. Not sure if I need to use power query or not for this. If not, any VBA code to accomplish the same would be great.
I want to automate the consolidation of data and processing it for 10 production lines.
Each production line has its own folder on the drive.
Within each line's folder, there is a monthly folder example - March, April etc.
Within each Monthly folder there is a day folder - May 1, May 2 , May 3, etc.
Within each day there are 3 excel files - Shift 1, Shift 2 and Shift 3.
Data for each shift for 10 different lines is collected and manually entered into its specific shift excel file by production operators.
There is a master file located elsewhere on the drive which processes data from these lines but currently it has to be manually transferred to the master file - which is a pain and super time consuming.
The master file contains 2 worksheets for each production line. I would like to copy and paste specific range of cells (H9:R24) from the shift production line files into specific worksheets within the master file specifically made for those production lines.
The column before the cell ranges where I would like to paste the data (in the master file) contains dates and hence I am hoping that the code/query will help me paste via matching the date from within the shift excel file.
Each shift excel file is exactly the same in terms of naming of columns, formatting, formulas etc.
Once pasted into the specific worksheet within the master file, cells in another worksheet (within the master file) use these numbers to process these via formulas and more numbers are generated such as waste % etc. which will be automatically plotted on different graphs.
I would like to super automate this process with a click of the button in the master file to access the data within those files, copy and paste into specific cells in specific worksheets within the master file and process data according to specified formula (already within the cell) to ultimately give processed numbers and graphs for each line for the previous day.
This needs to be repeated for 30 files - 3 files each for each day for 10 lines - every single day first thing in the morning.
Not sure if I need to use power query or not for this. If not, any VBA code to accomplish the same would be great.
Requesting you kindest help.
Thank you !
I am unable to upload a sample file as it contains sensitive data.
Hi Raj,
The file containing your query should be your ‘master file’, or you put the query in your master file. This way you can close and load the data to a table in the master file.
Tip: if you want to add calculations to columns in the consolidated data then it is more efficient to do that in Power Query before loading it to a table.
Mynda
Hi Mynda,
Correct !
But I dont know how I can link them and copy certain range of data.
Hi Mynda,
All I am looking for is a code or a way in power query to achieve the same is to copy a certain range of cells from each of the shift files into line specific worksheets within the master file. Once pasted, other cells which contain formulas based on these pasted cells will process the numbers and generate graphs.
Hi Raj,
I think you would do best to learn Power Query so you understand how you can leverage it for your project. I can tell by your questions that the copy and paste step you want to do can be done with Power Query, but it's not something I can easily explain without spending considerable time understanding your processes.
I prefer to teach you how you can use these tools so you can apply the techniques to your work. The forum is here to help you if you get stuck, not to do the project for you.
I have a Power Query course here: https://www.myonlinetraininghub.com/excel-power-query-course that will get you up and running quite quickly and I'm confident you'll find many uses for Power Query.
Please let me know if you have any questions about the course, or if you get stuck when trying to implement the techniques I teach in the course, then I'm here to help troubleshoot and point you in the right direction.
Mynda
Thank you Mynda for your reply.
Its an issue of time or else I definitely would have learnt it myself. I come from a non coding background hence am unable to quickly wrap my head around it.
Thank you and have a great day.
Hi Raj,
Power Query doesn't require coding. It's mostly point and click GUI. Sure, you can code it if you want, but most people won't need to.
If you want someone to build it for you then we can provide you with Excel consulting. Just contact us via email for more info.
Cheers,
Mynda
