Extracting data from multiple workbooks & multiple sheets into one summary workbook/sheet
I was wondering whether someone might be able to help me with this, I have recorded some macros to try and do this but it appears the code is going to be massively long, I'm sure there is probably a shorter way of doing the same thing.
I work for a company that has 2 sites with similar activities going on at both sites. Unfortunately data that is recorded for these same activities is not recorded in the same format in Excel. I have been asked to present a summary file of the data from both sites. The main site with the most data I have completed already which in itself was a large project. My manager is able to select a date and it will show him all the data from 50 different workbooks for that date.
What I am trying to do now is to get the data from the smaller site and present it in the same format as the main site. I cannot change the format of the excel file from the smaller site so I want to extract the data into a similar format as the main site and then manipulate it from there. Another way in which the two sites differ is that the main site with it's 50 units has 50 excel files with weekly data running consecutively down a single sheet which now includes over 6 years worth of data and runs into many thousands of rows. The layout is similar to the attached Weekly Summary.xlsx file. Unfortunately the smaller site has a new file created for each week and all the data from 15 units is presented on 7 sheets named SATURDAY through to FRIDAY which is how a week is classified here rather than Monday to Sunday. I also need to anticipate the new weekly file which will have exactly the same file name but with the week number changed. I'm not sure whether to split the data, once it's imported into the main file, into seperate weeks on individual tabs as I've done in the example Weekly Summary.xlsx file, I'd be quite happy if it ran consecutively down a single sheet, WEEK 01 units 1 to 15 and then WEEK02 units 1 to 15 and so on, I don't know which is the easiest to code.
If anyone could get me started on this I'd be very grateful.