Hi,
I would like to create a macro which finds data from multiple worksheets and collates them in my Master Worksheet.
I am competent with a lot of functions with Excel, however I have never used Macro's before. I have a little bit of VB knowledge, but only the very basics. I will attempt to explain my situation as clearly as I can.
Please note in your response that I am not familiar with a lot of the programming jargon. I also do not know how to actually create (or is it record?) a macro.
Finally, before I dive into it, I would *prefer* not to have to add code to the closed worksheets, but I can do this if there is no other way!
**********************************************
For every day, a new worksheet is created by my colleagues which summarises the previous days production. My task is to create a worksheet which locates only a few specific cells within the daily summary worksheet and then prints the values into my master worksheet. This allows me to look at a specific couple of pieces of equipment and their specific production. (Production is not a number per se, but rather text. I don't see how this would affect the code, though, as it's simply coppying a cell).
This may appear easy to program, but the twist is the daily summary files are in folders corresponding to their month and year. So Febuary 1st will have its file in a directory different to January 31st and January 1st will be in a different year and month directory to december 31st. The directories differ by name only (C:\\year\month) for example.
I have attached my Master Document. It is called "MFI to well match". In my opinion, it does not contain any sensitive, confidential or innapropriate contents. You can ignore columns K onwards, they're just some simple code I wrote to rearrange some formula. It may serve to open it now so you can follow me. I have not attached a daily summary file since it contains confidential information.
Cells B4 to J4 are pieces of equipment, called MFI's. On any particular day, an MFI records the production of a specific well. This is the information I need to find from each daily summary file. You will notice I have manually entered them from the 1st of January to the 24th. That was time consuming and I hope to never repeat that process!!!
Ideally, would like my macro to do this:
- I select the row corresponding to a date, say row 25, which has already had its macro find and print the required data
- I drag that row down to copy everything to the next days row, row 26
- While both rows are highlighted (or maybe just row 26), pressing a button called "update" in the top will go and find my data for that specific day i.e. updating row 26 with its corresponding daily summary data.
My colleagues have a spreadsheet which does this already for something similar. (The reason I'm not getting them to help me now is because they're all busy of course!!)
This might help you with the solution:
The open master worksheet name: "MFI to well match.xls"
The closed worksheet name: "APF Daily Report 24.01.10.xls" obviously for the 24th of January 2010.
The directory for that file we can call: "C:\Production\2010\January"
All of the cells in column B for "MFI to well match.xls" coresspond to cell $T$65 in the closed worksheets.
All of the cells in column C for "MFI to well match.xls" coresspond to cell $T$66 in the closed worksheets.
All of the cells in column D for "MFI to well match.xls" coresspond to cell $T$67 in the closed worksheets.
All of the cells in column E for "MFI to well match.xls" coresspond to cell $T$68 in the closed worksheets.
All of the cells in column F for "MFI to well match.xls" coresspond to cell $T$69 in the closed worksheets.
All of the cells in column G for "MFI to well match.xls" coresspond to cell $T$70 in the closed worksheets.
All of the cells in column H for "MFI to well match.xls" coresspond to cell $T$71 in the closed worksheets.
All of the cells in column I for "MFI to well match.xls" coresspond to cell $T$73 in the closed worksheets.
All of the cells in column J for "MFI to well match.xls" coresspond to cell $T$64 in the closed worksheets.
In advance, thank you so much for taking the time to help. It is very much appreciated. I am finishing work now, but I will call in tomorrow morning to check if any solutions have been found or if there are any further questions.
PS I hope the above wasn't too confusing.
Bookmarks