PDA

View Full Version : Solved: Close workbooks



Champers
06-25-2009, 03:16 AM
Hi all,

I have got a very simple problem which I can't get my head around.

When I open a workbook, it automatically opens the PMASTER2009 workbook as well with the following code, which is great.


Sub OpenMaster()
'
' OpenMaster Macro
'
Set wbOne = ActiveWorkbook
Workbooks.Open "S:\Eng\PMASTER2009.XLSB"
wbOne.Activate
'
End Sub

However when I close the active workbook I would like it too close the active workbook and the PMASTER 2009 saving all changes.

I have tried the following:


Sub CloseMaster()
'
' CloseMaster Macro
'
ActiveWorkbook.Close True
Workbooks.Close ""S:\Eng\PMASTER2009.XLSB"
wbOne.Close

End Sub

However I am not sure what else to try.

Any help would be much appreciated as it should probably be simple and I can't get my head around it.


Kind Regards


Champers

Simon Lloyd
06-25-2009, 03:24 AM
Assuming your code resides in the workbook that called the master, try this:
Sub CloseMaster()
'
' CloseMaster Macro
'
Workbooks("S:\Eng\PMASTER2009.XLSB").Close True
ActiveWorkbook.Close True
End Sub

Champers
06-25-2009, 03:48 AM
Hi,

I tried using the code, however for some reason the PMASTER2009 spreadsheet stays open.



Sub CloseMaster()
'
' CloseMaster Macro
'
Workbooks("S:\GTO_Operations\General\MIS\Master Workbooks\PMASTER2009.XLSB").Close True
ActiveWorkbook.Close True


End Sub

I am right in saying that when I click close on the active workbook the PMASTER2009 should close automatically?

Simon Lloyd
06-25-2009, 04:08 AM
Yep, my mistake, you have its as PMASTER2009.xlsb, xlsb is the extension used for excels base workbook and is usually called PERSONAL.xlsb, it normally opens whenever excel is opened.
perhaps put this in your calling workbook in the Thisworkbook code module:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
Workbooks("S:\GTO_Operations\General\MIS\Master Workbooks\PMASTER2009.XLSB").Save
Application.Quit
End Subit should work as you want.

p45cal
06-25-2009, 04:11 AM
deleted

Champers
06-25-2009, 04:26 AM
Sorry, I am still not getting it as I am not sure what the private sub means.

Do I add it to the close master Macro or is it a macro on its own.

2007 doesn't seem to recognise it.

Simon Lloyd
06-25-2009, 07:40 AM
The macro goes in the Thisworkbook code module:
How to Save a Workbook Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on any Worksheet's Name Tab
3. Left Click on View Code in the pop up menu.
4. Press ALT+F11 keys to open the Visual Basic Editor.
5. Press CTRL+R keys to shift the focus to the Project Explorer Window
6. Press the Down Arrow Key until ThisWorkbook is highlighted in blue.
7. Press the Enter key to move the cursor to the Code Window
8. Paste the macro code using CTRL+V
9. Save the macro in your Workbook using CTRL+S

You don't need any other macro.

Champers
06-25-2009, 07:54 AM
Hi Simon,

Thank you for your help.

It has wroked perfectly and is much appreciated.


Kind Regards


Champers

Simon Lloyd
06-25-2009, 10:22 AM
If your question has been solved please take the time to mark it that way by going to thread tools and Mark Solved.