Okay, here you go.
https://drive.google.com/open?id=0B8eLYugt7qKGMHl0Zl9NbFgxUlU
There's quite a bit to explain and I just got off of an overnight shift...but I'll try to remember everything.
1. I had to change some of the formatting on RCR in order to make everything work. The main thing I had to change was the Comments section...this section used to be merged across quite a few cells which was causing some annoying issues with the macros. It wasn't something that I wasn't able to overcome/figure out a workaround for...but changing this section to be one cell instead of merged across multiple smaller cells made things a lot easier. This had some unintended side effects with some of the other portions of the RCR worksheet...The "Road Condition Report" cell in row 2 and the two large cells below the "Subject:" cell all had to be reformatted to accommodate the changes to the Comments section. I tried to make everything look exactly as it did before I started mucking around...so hopefully you're satisfied there.
2. The Date cell on the Inspection worksheet is no longer formula based. Instead, it will be changed by the NewDay macro.
3. I added another column (N) to your table on Inspection. This column is hidden but has a pretty important purpose. It contains a formula (=SUMPRODUCT(--(E7:M7<>""))=0), which is looking at Columns E:M. If there is any data within that range, then the formula returns "False". If there is NOT any data, then the formula returns "True". This information is used in both the NewDay and CopyToRCR macros.
4. I changed your formula in the "Number of roads closed" section at the top of the worksheet to =COUNTA(G7:G1397) ..."COUNTA" will return the number of cells in a given range that are
not blank.
5. I added some code to the Worksheet_Change and Worksheet_SelectionChange events on the Inspection worksheet. As I have it now, when you run the NewDay macro, it will do quite a few things, but for the purpose of this particular bullet, it will erase all of the data that has been copied to RCR as well as that same data from Inspection. So Change and SelectionChange have been coded to notify you when it detects 1. You are making a change and 2. The NewDay macro has not been run yet. In this case, it will notify you that any data you change will be erased once NewDay is ran and it will ask you if you want to continue. If you choose yes, it will commit the change. If you choose no, it will discard the change and reapply the original value of the cell.
6. Regarding NewDay...I know you said you wanted to start with a fresh Inspection sheet for each day. Instead of creating a ton of additional worksheets over time, I decided it was probably best just to export the Inspection worksheet as PDF. So when you click the "New Day" button...First off, it's not going to run if the date cell matches the current date. It also won't run if it's not currently between 9 AM and 12 PM. This can all be modified/changed to your liking. If neither of those conditions are met, then it's going to 1. Filter the table using the hidden column (N) that I talked about earlier, to hide all of the rows that are blank. 2. Export the worksheet as a PDF file (will currently be saved in the same location as the workbook). 3. Delete all of the appropriate data from RCR and Inspection. 4. Reset the filter to default view (all visible).
Note: I chose to filter the results prior to exporting because I figured there was no reason to have 25 pages worth of roads when only 2 or 3 were closed the previous day. Filtering will only show the 2 or 3 (or however many) that were closed.
7. When you click the "Copy To RCR" button, it's going to do exactly that...1. Loop through all of the rows in your table. 2. Copy appropriate data to RCR. Note: Because of the way you have everything set up, you could run into some funkiness if there are more than 8 roads that are being copied...can also happen if multiple road names are too long and cause the row height on RCR to increase due to word wrapping. Basically, the "OFFICER//TITLE FORMULA//TOWN OF PORT HEDLAND" lines will get pushed off of the first page. Not exactly sure how I would go about creating a workaround for that.
8. That's all I can think of right now. Please let me know if you have any questions. I went through and pretty thoroughly commented the code, so you should be able to figure it all out. If you need help changing/modifying anything to suit your needs, please don't hesitate to ask!
