Re: close all other workbooks

Rob

Something like this will do it, suing the workbook_open() event

Private Sub Workbook_Open()
Dim wb As Workbook
Dim ThisWBName As String

ThisWBName = Me.Name
For Each wb In Workbooks
If wb.Name <> ThisWBName Then
wb.Close saveChanges:=True
End If
Next wb

End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk


"Rob" <Rob@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9BDF4A49-EC1B-40AD-A2DF-FEBE32C19566@xxxxxxxxxxxxxxxx
I have a workbook that is very large and I would like a simple code that
closes any open workbooks when I open this one. I have quite a few macros
in
this workbook and I tried to use the workbooks.close statement but of
course
that closes all workbooks. I don't see see anything like Before_Open as a
macro. Can someone please help.


.

Relevant Pages

  • Data Validation over 2 workbooks?
    ... I require the workbooks seperate for business reasons. ... Dim cboTemp As OLEObject ... Private Sub Worksheet_SelectionChange ...
    (microsoft.public.excel.newusers)
  • Re: Problem with Workbook_Activate event
    ... I replaced Application with ThisWorkBook, ... seperate variables to the other workbooks to minimize errors. ... Private Sub Workbook_Deactivate ... I need something which triggers when this remaining book appears, ...
    (microsoft.public.excel.programming)
  • RE: Problem with Workbook_Activate event
    ... Rather than use Application use Thisworkbook. ... seperate variables to the other workbooks to minimize errors. ... Private Sub Workbook_Deactivate ... I need something which triggers when this remaining book appears, ...
    (microsoft.public.excel.programming)
  • Re: Convert Macro into an Add-in file
    ... I think you're going to need an application event that'll look across workbooks. ... > Public OldRng ... > Private Sub Workbook_SheetSelectionChange(ByVal Sh As ... > Set ThisRng = OldRng ...
    (microsoft.public.excel.programming)
  • Re: Add-in to run macro when ANY workbook is opened?
    ... You could create a classmodule but ... Dim WithEvents xlApp As Application ... Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ... > reside in the individual workbooks, am i wrong in that assumption? ...
    (microsoft.public.excel.programming)