Out of range error for Windows().Activate

Question

I have a bit of code in my macro as follows

ChDir File_pth
Workbooks.Open filename:= File_pth & "\" & open_tkt
Workbooks.Open filename:= File_pth & "\" & closed_tkt
ActiveSheet.Select
Windows("MSS - Ticket Backlog Benchmark_2013 09 05.xlsx").Activate
Sheets("Extract -prev").Select

When I run the macro to open two other files, it should go back to the file in which I run macro to do some operations in the same Xl workbook.

I get the out of range error in the line

Windows("MSS - Ticket Backlog Benchmark_2013 09 05.xlsx").Activate

The file MSS - Ticket Backlog Benchmark_2013 09 05.xlsx exists.

Answer 1

You must place your macros in modules instead of in "This Workbook" or in specific sheets. Otherwise, you will have that window interaction problem. I had the very same issue and that's how I solved it.

Like this:

Answer 2

try

windows("MSS - Ticket Backlog Benchmark_2013 09 05").Activate

you could check the names of all open workbooks in Debug window like this:

dim oBook as workbook

for each obook in workbooks
    debug.print ">" & obook.name & "<"
next

Answer 3

Activating the original sheet should bring the original window to the front too, so you shouldn't need to play with the windows. (works for me anyway)

Dim Book As Workbook ' probably not needed
Set Book = ThisWorkbook ' probably not needed
Dim Sheet As Worksheet
Set Sheet = ActiveSheet
ChDir File_pth
Workbooks.Open filename:= File_pth & "\" & open_tkt
Workbooks.Open filename:= File_pth & "\" & closed_tkt
Book.Activate() ' probably not needed
Sheet.Activate()

I've added code to activate the original workbook before activating the original sheet (lines with the comments at the end) and as it says, they are probably not needed.

The variables Book and Sheet are references to The active workbook (ThisWorkbook) and the active sheet (ActiveSheet). This is what @mehow was suggesting.