Ok, firstly an overview 
I have a 'Summary' workbook which collates values from a series of 'sub-workbooks', (and can also update values in those sub workbooks).
the subworkbooks are setup so that when they are saved they also copy certain values out to another 'backup' file.
So there are 2 possible routes that the files will be used in:
1) SubWorkbook opened directly
- User opens SubWorkbook and makes some changes.
- User closes the SubWorkbook saving changes, or clicks the save button. The BeforeSave event opens the "restore" workbook, copies over the key values from the subworkbook, then saves & closes the "restore" workbook.
2) SubWorkbook opened remotely
- User / Admin opens the "Summary" workbook and changes an option.
- The summary workbook opens a subworkbook and makes changes, then saves it. The subworkbook should (as before), then open the restore workbook and save the key values etc.
The problem is in the second scenario the 'restore' workbook doesn't get opened.
I have created a set of 3 workbooks to illustrate the problem here.
Book1.xls = Summary workbook
Book2.xls = Subworkbook
Book3.xls = Restore workbook.
If you open book2 and then save it the 'BeforeSave' event will cause it to open book3 and write the output of 'Now()' to the next free row of book3-columnA, before then closing and saving book3.
If you open Book1 and run the only macro present then it will open Book2 and write the output of 'Now()' to the next free row of book2-columnA, before then closing and saving book2, which in turn triggers the 'BeforeSave' event of Book2, but book2 doesn't manage to open book3 
I've tried using .Close savechanges:=True as well as [i].Save[//i] followed by .Close savechanges=False in Book1 in case the Excel was refusing to open a workbook whilst another was saving but it made no difference.
Here is the code for the test:
Book1.xls - Standard module
Sub test_saving()
Dim wb As Workbook
Workbooks.Open Filename:=ThisWorkbook.Path + "\Book2.xls", _
UpdateLinks:=False, _
ignorereadonlyrecommended:=True
Set wb = Workbooks("Book2.xls")
With wb.Worksheets(1)
.Cells(.UsedRange.Rows.Count + 1, 1).Value = Now()
End With
wb.Close savechanges:=True
End Sub
Book2.xls - "ThisWorkBook" section:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wb As Workbook
Workbooks.Open Filename:=ThisWorkbook.Path + "\Book3.xls", _
UpdateLinks:=False, _
ignorereadonlyrecommended:=True ' Doesn't work if Book1 opened thisworkbook, but does if opened directly!
Set wb = Workbooks("Book3.xls")
With wb.Worksheets(1)
.Cells(.UsedRange.Rows.Count + 1, 1).Value = Now()
End With
wb.Close savechanges:=True
End Sub
Any ideas of a workaround or solution to this?
Thanks
Bookmarks