I have the attached file which contains 2 small macros. They are Basically the same macro but one copies a wider range of dates than the other. The macro will filter each sheet in the workbook (except for the first and last sheets which the macro buttons as these are summary sheets) for the selected dates and then copy those into a summary sheet and short it by date. Each sheet is in identical format. The macro and the use of autofilter seems to be a bit inefficient and a bit flaky. If any user leaves a blank line the autofilter obviously stops working at that point which screws the whole summary, it also seems to be creating an extra blank 'used' line at the bottom of the summary sheet each time the macro button is pressed (as seen by pressing ctrl-end which should stop on the bottom right cell of the data table). So if possible I would like to make the macro a little more robust.
The main issue is that I have been using this as a shared workbook and it has been a nightmare, randomly locking up simply by a user pressing the save button! So my plan is to split the individual sheets out into their own non-shared workbooks and just have this workbook with the master and export sheets in it and have the macro cycle through every workbook in the current folder (except for this summary file) and pull off the data into this file, the same wya the current macro does with each sheet within the file. I can then keep it a clean folder with just the users workbooks and this summary in it and when new users come along it will simply pick them up when a file is created for them.
I had a macro years ago doing something similar but cannot find it and don't have the experience of pulling data out of other files in this way to even beging to do it. So it would be very useful if someone could modify the existing macro to do it (if possible making it slightly more robust at the same time so it only takes columns A to BP and nothing further, and isn't broken by the first blank line it finds).
thanks
Bookmarks