Hello Everyone,
I am creating a Master work book to pull data from 15 files in a Folder every quarter. For starters, I need a macro that will loop through all 15 of those files (preferably in alphabetical order) and copy from the Net Operating Income line in Work Sheet “Current Year Budget” (for simplicity it will always copy from row B26:M26 in each of the workbooks) and Paste that line item in my working excel file starting in b2 with the first file then b3 with the second file, etc…
The Issue comes with the other set of data I need to copy and paste from each file. In the “Cashflows” worksheet of each file, I need to copy data out of row 51 “Capex”. Row 50 “Dates” contains each month from whenever I made the investment to now and then for the next 25 years. Currently, in cell A2 at the top of the page, I have in the date 1/1/2019. I need the macro to find this date (the one in A2) in row 50 (each of the 15 files has a different number of columns in this row) and start from the corresponding capex cell below it in row 51. It will need to copy for the next 10 years, which will be exactly 120 cells to the right from wherever it starts. It will then need to paste this into cell b3 in my working excel of tab “capex”, assuming it is alphabetized, and b4 for the next one, and so on and so on.
I would like the macro to only have to open each file once, copy the necessary NOI stuff, and paste, then copy the Capex stuff based on my deisred date in cell A2, and then paste in my working file, then close that file, and go to the next one.
Please let me know if you need clarification.
Thanks
