I have a (possibly very silly) question about Excel macros but I can't seem to find an answer anywhere else. My problem is this: I have an Excel file open all day and I updated it throughout the day when needed. My boss has another Excel file with a macro. Whenever he runs that macro, he is supposed to get the latest information from my file into his spreadsheet. What he actually gets though is the information from the last point I saved my file. So, even though I've set autosave to run every minute, if I last saved my file 2hrs ago, my boss will see my contents as off 2hrs ago and not anything else I've done since then. Is there anyway to bypass this without the macro closing/saving/interfering with my file at all?
I appreciate your help!
I bet you would love it if your workbook saved every single time anything changed on it, now that the formatting and arrangement are the way you want it. This way, the moment you hit ENTER on your keyboard, the file would be accessible to your boss.
I suggest forcing your workbook to save every time something changes. Add this code to the worksheet_change event:
Activeworkbook.save
Find the worksheet_change event like this:
Note that this code will make you crazy if it takes a long time to save or if you have a lot of things to change all at once. So be prepared to get rid of it. (Hit Alt-F11 to access the programming window)
Somebody smarter than me wrote some more about doing this, when the updates affect only a certain range of cells... http://www.dummies.com/software/microsoft-office/excel/using-macros-to-save-workbooks-when-cells-change/
Have fun!