Close File At Park

[nigelog]

Hi all
I need help with a macro that upon close the cursor will return to cell A5 on the control sheet of a workbook and save the file before closing. The problem I have had is that the main workbook when in use can open a further 10 workbooks all also with a control sheet. Every one that is opened also needs to be saved as above on close. I tried a macro but as all workbooks contain a sheet called control sheet the macro was losing its way and reporting excel errors. Cannot rename the individual control sheets of the workbooks as to many correlations. Any help appreciated. rgds nigel

[broro183]

hi Nigel,

I'm just about to leave so this is typed straight into the mesage & is untested but even if it doesn't work (there maybe small syntax errors on my part) it should provide the basic principles for you to manipulate...

Please Login or Register  to view this content.

HOpefully this doesn't have too many coding errors & you can apply the principles.
If it does have errors you can't solve, post back &/or try Googling for ("close all macro" "for each" sub excel)


Rob

[nigelog]

Will try this when have 5min spare. Many Thanks

[mikerickson]

I'd put this in the ThisWorkbook code module for each workbook

Please Login or Register  to view this content.

[broro183]

I like that Mike - I didn't realise that the GOTO method worked even when you're on a separate sheet, it makes sense though now that I think about it.


Rob

[nigelog]

Tried GOTO method but got a lot of "excel has encountered a problem messages". Will try adding to all sheets except main control sheet so that sheet saves when closed but main control menu is still main view and cursor is directed to it.
ta nigel

[broro183]

Hi Nigel,

I've tested my suggested code now & have made some changes. You may want to modify it slightly to do what you want for hidden files.

Please Login or Register  to view this content.

I'm not sure exactly what problem Excel would encounter with Mike's code but if you put this modified version in the "This Workbook" module of each file it should work:
(the on error statements in both codes allows for the lack of a sheet called "Control" in the file.)

Please Login or Register  to view this content.

hth
Rob

[nigelog]

Hi Rob
I will be back to work on that particular section in the next few days and I will let you know how I get on. Many htanks for persevering. Rgds Nigel