Macro to search and report findings in another directory

Hi all,
I need help creating a macro. I need it to look at my cities and go into a directory in a shared drive and look up the folders for those cities and look in a folder called "2016", then look for the current months file and in it look in the tab called "Summary" and report back if they filled out info for that day.

For instance, I have attached two files. One is the Report file which is where I want to run the macro from. The other is the Log file which is where my cities will log their info. From here i need it to look at the city on column A on my Report file, go to the shared drive location (i.e. C\path1\path2\"city") and in there find a folder called "2016" (this folder "2016" will be the same in all cities). This folder has 12 Log files, one for each month labeled "1 Jan Log", "2 Feb Log", "3 Mar Log" ..etc. In these Log files, there is a summary tab which is where I want it to look and see if they completed the log for that day. The total on row 13 for that respective day will have something other than 0 if it was completed, which i want it to return that on my Report file under the column for that day for that city. Hope this makes sense.

Any help is greatly appreciated!

I think I understand most of the requirement, but I need a better explanation about the files from which you get the data and on the log file.

What is the format of the files from which you get the data? Are the *.xls, *.xlsx or what? Also what does the data on a sheet within these files look like? You mention getting a total on Row 13. But what Cell? How is the data laid out on the sheet?

For the log files, do you mention 1 Jan Log, etc. Don't you need these broken out by city? Do you want a separate file for each city and a tab for each city within?

Finally, is the number of cities fixed, or do you anticipate adding cities in the future?

It's been a week. I'll have to take another look at it and "relearn" the requirement.

Where do you want your log file? Also, I took a look at the log file. If you have the date down column A, then why do you need days on row 3?

I'm having a difficult time visualizing the directory structure with dates, city names, month names, etc. Can you draw up a directory structure (picture)for where you get your data and where you want to write the logs?

Also can you provide a zipped set of folders with sample data? I set one up, but it was very limited in scope for a specific date and I am not sure I have it right. I would be spending more time setting up the data than actually coding.

Due to the difficulty setting up the data, I did minimal QA on this and there are still some things to be completed. I think I have the directory hierarchy figured out.

From the "root" data directory you have sub directories of city names. Each city name has subdirectories of years. Each year has a monthly file. Each monthly file has daily tabs.

I set up the log directory in almost similar fashion. From the "root" log directory there are sub directories of city names. Each city name has a log file with the format YYYY Log.xls. In each of these files are tabs 1-Jan, 2-Feb, 3-Mar …

The program loops through the list of cities. These are in cells starting in cell A14 The program only looks at the number of cites you have. As you add cities the list will grow and the program will accommodate them. The city list is actually an Excel table.

The program uses the date parts, city name, etc. to find the right path to the right file and the right sheet in that file. It checks cell A13 and if it isn’t blank it adds a “Yes” next to the date in the log file.

The data files must exist. The log files must exist. You will need to add additional pages for the log files. I’ve only taken them as far as April. You can “clone” the pages and set the month start date in cell A1. I do not do any checks in the program to see if the files or pages exist.

It is your responsibility to assure that they do exist.

A potential future enhancement will do these checks and if the log file doesn’t exist, the program will create it. If the appropriate monthly sheet does not exist, the program will create it. If the data file or sheet does not exist, it will notify the user and exit gracefully rather than crashing.

I just noticed an error in my code. I will fix it tomorrow.

Here is the file along with the directory structure. Remember you will be responsible for assuring that the log and data files exist. You can copy the sheets in one of the log files to fill our a sheet for every month. All you will have to do is change the date in cell A1 on each sheet and rename the sheet. Once you have one workbook done up this way, then you can copy it to the log directory under the various city names.

In 2017, make a copy of one of the logbooks. change the name, change the dates, clean out the old data, and copy it to the log directory.