Runtime Error '9' - Subscript Out of Range

[Ben]

We have 5 spreadsheet which reside on a network drive. The
spreadsheets are macro driven, and we have issue that only the person
who setup the macro's is able to run them. For everyone else, we
receive Runtime Error '9'. We have tried copying to local drive, and
same issue. Only the creater of the macro's can run them, and this
person is leaving the company. They are unable to figure this out.

When I receive error and click Debug...it goes to following location
in the macro:

For i = 0 To NumOfLeads 'adjust TL's sheets
Windows(PageName(i)).Activate (THIS LINE IS HIGHLIGHTED
YELLOW)
For j = 1 To 4
Worksheets(Sheets(j).Name).Activate
Rows(CopyValue).Copy
Rows(RNValue).Select
Selection.Insert Shift:=xlDown
Next j
Next i

Any ideas what cause might be? We all have same version of
Excel....and same version of VB.

 

[Dave Peterson]

What's in pagename()?

If it's just the name of the workbooks, does it include the extension (like
..xls)?

 

[Ben]

pagename() is just the tab she wants to activate. and as i said...she
has no problem running the macro. even when we all attempt to run
from network drive...she is able and we can't without the runtime
error.

I can't figure out if something is maybe hardcoded that only reads
correctly from her desktop...or what cause might be. Seems the macro
itself...if it runs for one person, isn't necessarily the issue.

 

[Dave Peterson]

I don't think so.


is where she's looping through the worksheets/tabs in the workbook.

pagename() should hold the names of the windows.

Unless you have multiple windows open in a workbook, then this should be an
array of workbook names.

 

[Ben]

he reason it's not an array, because by definition there will always
be multiple windows open - there are 5 files open at any time you run
the macro. the macro updates all 5 spreadsheets, plus each of 3 tabs
in each spreadsheet.

She just tried it on someone else's machine...and it worked. So
somehow, somewhere...it's in the way something is setup.

 

[Dave Peterson]

It sure looks like pagename() is an array to me.

And if your workbooks all have 3 worksheets, then you should be getting an error

The code is trying to look at 4 worksheets.

Again, try to find out what the elements in pagename() are.

You could add code like:

For i = 0 To NumOfLeads
msgbox PageName(i)
next i

to help you debug it.

 

[Ben]

It sure looks like pagename() is an array to me.

And if your workbooks all have 3 worksheets, then you should be getting an error


The code is trying to look at 4 worksheets.

Again, try to find out what the elements in pagename() are.

You could add code like:

For i = 0 To NumOfLeads
  msgbox PageName(i)
next i

to help you debug it.

Dave...thanks for your help. Looks like a reinstall of Excel is fix
for this issue. No idea why...but seems to be working.

 

[Dave Peterson]

I'm surprised that reinstalling excel would have helped this problem, but glad
you got it working.

Dave...thanks for your help. Looks like a reinstall of Excel is fix
for this issue. No idea why...but seems to be working.

<<snipped>>