Asked by:

Macro to copy data

Question

I am after developing a macro which will copy different pieces of information from another file. My issue is that these files are arranged in monthly folders, and new ones are being added all the time. It would be easy if the data came from the same file
all the time but it doesn't. Every four days or so depending on production, a new excel file is added with a different name that the data has to come from.

Can this be done? Or is it not possible because the file the information comes from isn't the same one every day?

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for
your understanding.

I am after a macro which is able to look in a number of different sheets of a file to pull out information and dump it onto a summary spreadsheet, which is a separate file. Each spreadsheet has four sheets with eleven pieces of information in each sheet.

Another issue is that there are new files added a few times a week which I would also need the information taking from. These are arranged month by month in a folder for each month.

So I basically need a macro which is A) capable of looking across multiple cells on four sheets and pulling the information back into my summary sheet B) able to identify when a new file has been added and look at this too C) Look in the monthly folders
where these files are stored.

Even though the information is spreadsheet out over multiple spreadsheets per month, the information required over the four sheets is always in the same cells.

you can change the path of initial file name and set that folder path where you store all the files.

in that folder you can sort the files, so when you open the file dialog you can see the new latest file.

so you can easily find the file.

you can modify the code as per your requirement.

Regards

Deepak

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to
MSDN Support, feel free to contact MSDNFSF@microsoft.com.

If I were you I would record a Macro to do the work. Then, look at the code and find the date part. Put that in a variable, like maybe in a cell. Something like =TODAY(), or =TODAY()+4. Finally, reference the sheet and cell (range)
in your Macro. Run everything through the Task Manager, like in the example below.