close all other workbooks
 

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.

close all other workbooks
 

Hi Rob,

Try:

'=============
Private Sub Workbook_Open()
Dim WB As Workbook

For Each WB In Application.Workbooks
If WB.Name < Me.Name Then
WB.Close SaveChanges:=True 'Or False?
End If
Next WB
End Sub
'<<=============

This is workbook event code and should be pasted into the workbook's
ThisWorkbook module *not* a standard module or a sheet module:


---
Regards,
Norman



"Rob" wrote in message
...
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.

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" wrote in message
...
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.

close all other workbooks
 

Thanks Norman but I forgot to mention one thing, others will be using this
workbook and saving it under different names so I will not know the names of
the workbooks. Really what I would like to do is that when one opens this
workbook, all other workbooks close. What do you think?

"Norman Jones" wrote:

Hi Rob,

Try:

'=============
Private Sub Workbook_Open()
Dim WB As Workbook

For Each WB In Application.Workbooks
If WB.Name < Me.Name Then
WB.Close SaveChanges:=True 'Or False?
End If
Next WB
End Sub
'<<=============

This is workbook event code and should be pasted into the workbook's
ThisWorkbook module *not* a standard module or a sheet module:


---
Regards,
Norman



"Rob" wrote in message
...
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.

close all other workbooks
 

Nick, thanks but I forgot to mention that I won't know the name of the
workbook since there will be many people using this tool and saving it under
different names.

"Nick Hodge" wrote:

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" wrote in message
...
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.

close all other workbooks
 

Hi Rob,

The suggested code is independent of the file name.

In the expression;

If WB.Name < Me.Name Then

Me.Name refers to the workbook holding the code.


---
Regards,
Norman