Excel Close All Open Worksheets

[Happy Harry]

I currently open 18 worksheets each day and the "Log Multiple Sheets QuickLink" works great.

On closing the day off though, I first of all have to manually update each sheet -6 in cell Q2 of the the market pane for each sheet. Then close BA because if you dont close it before you start closing the sheets excel spits the dummy because they are locked - a good thing so no complaint about that - and then one sheet at a time click on the sheet, use either Alt-F4 or click on the x on the top right, click "yes" to resave the information, just in case. Move to the next sheet. Rince and repeat.

Does anyone have a way or bit of code to:-

1. Close BA.
2. Close all the linked sheets saving [or for that matter not saving] them from a single click.

Note because of the linking issues BA MUST be closed before you close the sheets or I end up with all sorts of problems with corrupted sheets. So in terms of the question, if the closing BA has to be done manually and then single click to close everything else then thats still a big improvement.

Thanks

[brumbie]

I have the same problem but with only 4 so I feel for you.However I prefer to close mine separately as 2 of them are record playback sheets that need to be saved inside the workbook first. I found this though a while back that may help you:

http://www.dummies.com/how-to/content/an-excel-macro-to-close-all-workbooks-at-once.html

I guess it would go in the personal.xlsm:

Sub Macro1()
'Step 1: Declare your variables
Dim wb As Workbook
'Step 2: Loop through workbooks, save and close
For Each wb In Workbooks
wb.Close SaveChanges:=True
Next wb
End Sub

[brumbie]

Thinking about it I'm not sure the personal.xlsm would be open whilst in BA so I understand you now.....

[brumbie]

Try this page:

https://www.extendoffice.com/documents/excel/2335-excel-close-all-workbooks.html

[alrodopial]

ideas
1. use 18 sheets inside the same worksheet/workbook
2. this code - as example - may be prooved helpful

Code: Select all

saveNAME = ThisWorkbook.Sheets("Sheet1").Range("AA1").Value
ThisWorkbook.SaveCopyAs Filename:="C:\Excel files\" & saveNAME & ".xlsm"

3. using a mouse recorder software (free) will do all the things you want by the press of a button
ok not so "easy" to achive it daily but it can be done

[Happy Harry]

ideas

3. using a mouse recorder software (free) will do all the things you want by the press of a button
ok not so "easy" to achive it daily but it can be done

Yep I reckon this might be the way to go.

Thanks

HH

[Happy Harry]

Hello Mark and Gary

unfortunately AutoHotKey no longer includes a macro generator for the new version. And I have migrated to Windows 10.

BUT Mark and Gary. The Excel Log Multiple Sheets Quicklink is great. Works everyday without a hitch. Now that uses a text file to store its information. So may I request, very easy for you to do but difficult for me because I dont know VBA, that you add another drop down on the same Excel Tab to do exactly the opposite of opening the sheets.

So "CLOSE BA AND ALL LINKED SHEETS"

That would then.

1. Update each sheet to grab the latest results. - -6 in Q2 ? Waiting to ensure the information was actually updated.
2. Unlink Each Sheet using the same list as the Multiple Sheets Quicklink
3. Close each of them.
4 Close BA.

If I knew VBA I am sure I could do it myself but alas I dont.

Thanks

HH