I have a macro that is supposed to save and close open workbooks and
(theoretically) will not produce an error if they are not open. I have posted
the macro below.

I keep getting a periodic error message when I run the macro for many
workbooks where some are already shut (around 12) but if I keep re rerunning
the macro it eventually works completely.

I get the error
Run time error €˜9
Subscript out of range

Can anyone help me, fix this so it works on the first time with no error
message?

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 for 12 more different 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 a macro that is supposed to save and close open workbooks and
(theoretically) will not produce an error if they are not open. I have posted
the macro below.

I keep getting a periodic error message when I run the macro for many
workbooks where some are already shut (around 12) but if I keep re rerunning
the macro it eventually works completely.

I get the error
Run time error €˜9
Subscript out of range

Can anyone help me, fix this so it works on the first time with no error
message?

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 for 12 more different workbooks.

End Sub

Thanks Tom, this works perfectly now.