VBA to pull data from closed workbooks

RE Dev · Feb 1, 2024

First off, I have read several threads and thought I was getting close to piecing it together, but cannot figure it out, so I am hoping someone has a solution they'd be kind enough to share. I am trying to pull data from closed workbooks within the same folder to a summary/destination workbook. All of the excel files are from the same template, so they have the same worksheet names, same named ranges I need to pull, etc. I'd like to ideally have the macro search all excel files within the folder and paste those values into my destination workbook in separate rows. One example is a named range of "Project_Name".

Additionally, we store our files on onedrive, so if it's possible to incorporate code to determine the local path instead of the onedrive path, that would be phenomenal, but if not, I think I can source that code and modify your solutions to fix that piece. Thanks!

Logit · Feb 1, 2024

I may or may not have the answer you are seeking. One thing that would assist me and other volunteers is if you posted a copy example of your proposed "Destination Workbook" where what is copied from the other workbooks will be pasted as the "Master Workbook". Provide a few lines of 'mock up data' how the pasted data will look.

Also provide an example (mock up) of one of the workbooks that will be copied from. Please have that data match an example shown in the Destination Workbook.

This forum doesn't provide a means to include files with your postings. You'll need to post your example workbooks to a 'cloud website' and provide the download link here.

Thanks

RE Dev · Feb 2, 2024

Thank you for the feedback. I am trying to include a sample here, so hopefully it works. Ultimately, I am trying to pull data from the various source workbooks into a destination workbook without having to manually link each cell from each source workbook. In this example, I am looking for sales by month of each project (each it's own source workbook) and then displaying these sales in the destination workbook by showing each project by month and then summing them for the total sales per month.

Example problem for mr excel.xlsx