I have this macro which is aimed to close a series of workbooks which may or
may not be open. it works perfectly when all the workbooks detailed are open
but tends to get periodically stuck if some of them are shut (although if I
keep running the macro eventually everything will close down. When I debug it
says

"Run time error €˜9
Subscript out of range"

is there anyway to get this macro running smoothly as it is really
frustrating that is isn't working completely the first time.

Sub SaveAndCloseWorkbooks()

Dim wbtest As Workbook

On Error Resume Next
Set wbtest = Workbooks("Bund Current Payoff.xls")
On Error GoTo 0
If Not wbtest Is Nothing Then Workbooks("Bund Current Payoff.xls").Close
SaveChanges:=True

On Error Resume Next
Set wbtest = Workbooks("Gilt Current Payoff.xls")
On Error GoTo 0
If Not wbtest Is Nothing Then Workbooks("Gilt Current Payoff.xls").Close
SaveChanges:=True

etc and so forth for 9 more workbooks

End sub

Sub SaveAndCloseWorkbooks()

Dim wbtest As Workbook

set wbTest = Nothing
On Error Resume Next
Set wbtest = Workbooks("Bund Current Payoff.xls")
On Error GoTo 0
If Not wbtest Is Nothing Then _
Workbooks("Bund Current Payoff.xls").Close _
SaveChanges:=True

set wbTest = Nothing
On Error Resume Next
Set wbtest = Workbooks("Gilt Current Payoff.xls")
On Error GoTo 0
If Not wbtest Is Nothing Then _
Workbooks("Gilt Current Payoff.xls").Close _
SaveChanges:=True

set wbTest = Nothing

etc and so forth for 9 more workbooks

End sub

--
Regards,
Tom Ogilvy


"Cammy" wrote:

I have this macro which is aimed to close a series of workbooks which may or
may not be open. it works perfectly when all the workbooks detailed are open
but tends to get periodically stuck if some of them are shut (although if I
keep running the macro eventually everything will close down. When I debug it
says

"Run time error €˜9
Subscript out of range"

is there anyway to get this macro running smoothly as it is really
frustrating that is isn't working completely the first time.

Sub SaveAndCloseWorkbooks()

Dim wbtest As Workbook

On Error Resume Next
Set wbtest = Workbooks("Bund Current Payoff.xls")
On Error GoTo 0
If Not wbtest Is Nothing Then Workbooks("Bund Current Payoff.xls").Close
SaveChanges:=True

On Error Resume Next
Set wbtest = Workbooks("Gilt Current Payoff.xls")
On Error GoTo 0
If Not wbtest Is Nothing Then Workbooks("Gilt Current Payoff.xls").Close
SaveChanges:=True

etc and so forth for 9 more workbooks

End sub

To add to that, might want to consider the following:

Sub SaveAndCloseWorkbooks()

Dim N as Integer

For N = 1 to Workbooks.Count
With Workbooks(N)
.Close SaveChanges:=True
End With
N = N - 1
Next

End Sub

"Tom Ogilvy" wrote:

Sub SaveAndCloseWorkbooks()

Dim wbtest As Workbook

set wbTest = Nothing
On Error Resume Next
Set wbtest = Workbooks("Bund Current Payoff.xls")
On Error GoTo 0
If Not wbtest Is Nothing Then _
Workbooks("Bund Current Payoff.xls").Close _
SaveChanges:=True

set wbTest = Nothing
On Error Resume Next
Set wbtest = Workbooks("Gilt Current Payoff.xls")
On Error GoTo 0
If Not wbtest Is Nothing Then _
Workbooks("Gilt Current Payoff.xls").Close _
SaveChanges:=True

set wbTest = Nothing

etc and so forth for 9 more workbooks

End sub

--
Regards,
Tom Ogilvy


"Cammy" wrote:

I have this macro which is aimed to close a series of workbooks which may or
may not be open. it works perfectly when all the workbooks detailed are open
but tends to get periodically stuck if some of them are shut (although if I
keep running the macro eventually everything will close down. When I debug it
says

"Run time error €˜9
Subscript out of range"

is there anyway to get this macro running smoothly as it is really
frustrating that is isn't working completely the first time.

Sub SaveAndCloseWorkbooks()

Dim wbtest As Workbook

On Error Resume Next
Set wbtest = Workbooks("Bund Current Payoff.xls")
On Error GoTo 0
If Not wbtest Is Nothing Then Workbooks("Bund Current Payoff.xls").Close
SaveChanges:=True

On Error Resume Next
Set wbtest = Workbooks("Gilt Current Payoff.xls")
On Error GoTo 0
If Not wbtest Is Nothing Then Workbooks("Gilt Current Payoff.xls").Close
SaveChanges:=True

etc and so forth for 9 more workbooks

End sub