subscript out of range

[Ben]

Hello,

I have a script I have been running which has suddenly run into a
"Runtime error '9' - subscript out of range" error.

The original code had this:
Selection.Copy
ActiveWindow.ActivateNext
Sheets("Spending").Select

And I changed it a while ago to this:
Selection.Copy
Windows("finances_2009.xlsx").Activate
Sheets("Spending").Select

And eventually to this:
Selection.Copy
Windows("finances_" + Right(Str(Year(Date)), 4) + ".xlsx").Activate
Sheets("Spending").Select

Only the top version now works. And the later 2 do not giving me the
subscript error. Any ideas how I can fix this?

Thanks,
Ben

 

[Don Guillett]

Run this to see what you get. Both result in
finances_2009.xlsx
Do you have such a workbook?

Sub tryit()
MsgBox "finances_" + Right(Str(Year(Date)), 4) + ".xlsx"
MsgBox "finances_" & Year(Date) & ".xlsx"
End Sub

 

[Dave Peterson]

If the code is breaking on the Windows line, then maybe you don't have a window
named: finances_2009.xlsx

If you have multiple windows open, maybe the window captions look like:
finances_2009.xlsx:1
and
finances_2009.xlsx:2
and ...

If the line that's breaking is the Sheets("Spending").select line, then maybe
the window that you're trying to activate has a visible worksheet named
"Spending".