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.

What's in pagename()?

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



Ben wrote:
>
> 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

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.


On Jul 21, 9:47*am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> What's in pagename()?
>
> If it's just the name of the workbooks, does it include the extension (like
> .xls)?
>
>
>
>
>
> Ben wrote:
>
> > 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- Hide quoted text -
>
> - Show quoted text -

I don't think so.


> > > For j = 1 To 4
> > > Worksheets(Sheets(j).Name).Activate

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 wrote:
>
> 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.
>
> On Jul 21, 9:47 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > What's in pagename()?
> >
> > If it's just the name of the workbooks, does it include the extension (like
> > .xls)?
> >
> >
> >
> >
> >
> > Ben wrote:
> >
> > > 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- Hide quoted text -
> >
> > - Show quoted text -

--

Dave Peterson

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.



On Jul 21, 10:49*am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> I don't think so.
>
> > > > * * * * For j = 1 To 4
> > > > * * * * * * Worksheets(Sheets(j).Name).Activate
>
> 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 wrote:
>
> > 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.
>
> > On Jul 21, 9:47 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > What's in pagename()?
>
> > > If it's just the name of the workbooks, does it include the extension(like
> > > .xls)?
>
> > > Ben wrote:
>
> > > > 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- Hide quoted text -
>
> > > - Show quoted text -
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

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
on these lines:

> > > > > For j = 1 To 4
> > > > > Worksheets(Sheets(j).Name).Activate

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 wrote:
>
> 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.
>
> On Jul 21, 10:49 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > I don't think so.
> >
> > > > > For j = 1 To 4
> > > > > Worksheets(Sheets(j).Name).Activate
> >
> > 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 wrote:
> >
> > > 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.
> >
> > > On Jul 21, 9:47 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > > What's in pagename()?
> >
> > > > If it's just the name of the workbooks, does it include the extension (like
> > > > .xls)?
> >
> > > > Ben wrote:
> >
> > > > > 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- Hide quoted text -
> >
> > > > - Show quoted text -
> >
> > --
> >
> > Dave Peterson- Hide quoted text -
> >
> > - Show quoted text -

--

Dave Peterson

On Jul 21, 11:45*am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> 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
> on these lines:
>
> > > > > > * * * * For j = 1 To 4
> > > > > > * * * * * * Worksheets(Sheets(j).Name).Activate
>
> 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 wrote:
>
> > 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.
>
> > On Jul 21, 10:49 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > I don't think so.
>
> > > > > > * * * * For j = 1 To 4
> > > > > > * * * * * * Worksheets(Sheets(j).Name).Activate
>
> > > 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 shouldbe an
> > > array of workbook names.
>
> > > Ben wrote:
>
> > > > 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.
>
> > > > On Jul 21, 9:47 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > > > What's in pagename()?
>
> > > > > If it's just the name of the workbooks, does it include the extension (like
> > > > > .xls)?
>
> > > > > Ben wrote:
>
> > > > > > 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 ISHIGHLIGHTED
> > > > > > 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- Hide quoted text -
>
Dave...thanks for your help. Looks like a reinstall of Excel is fix
for this issue. No idea why...but seems to be working.

> > > > > - Show quoted text -
>
> > > --
>
> > > Dave Peterson- Hide quoted text -
>
> > > - Show quoted text -
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

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

Ben wrote:
>
<<snipped>>
> 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>>
--

Dave Peterson