run-time error '9' subscript out of range 2007 excel

[rulo777]

Hi I am using Microsoft Excel 2007.

I get the 'Run Time Error '9' Subscript out of range' when running the macro that I use everyday. This macro formats a report I create everyday and it is available for other users so the macro is on a shared drive and everyone can use it. The weird thing is that I wasthe only one receiving this messsage. if I run the macro from other computer it was working smoothly. I was only experiencing this problem on this specific work station. I was running it from other stations but now it gives me the same error on every computer.



If I click Debug it highlights the following line in my macro:



Windows(MyFile).Activate


Help would be greatly appreciated!

Thanks



I am copying the code of that module:

Please Login or Register  to view this content.

[stojko89]

My god....please use CODE tags, Edit your post and put the code in CODE TAGS! so the code will be readable

[rulo777]

Done, does that works for you?

[JBeaucaire]

You probably could Google that error message and get the basis of the problem pretty quickly. "Subscript out of range" errors invariable mean: "Some object (sheet, workbook, named range) that you gave me the name for to use.... Excel can't find that object."

The line of code supports that. MyFile is a string you got from J3. What exactly is that string? If even 1 character varies from the exact name of an already open workbook, it won't work.

Also, if the name is exact, is it already open? I'd recommend you set a workbook variable instead of a string, because then you can address the workbook from then on without using the real name again, just the wb variable.

Also, once you have the wb variable, you don't have to "activate" or "select" the objects anymore, you can just send data directly to the objects.

Here's as much of that macro as I could decipher to get you started rewriting it addressing those objects directly:

Please Login or Register  to view this content.